SAS MACRO 初步檢查文字變數內是否有亂碼

%macro Garbled(DS=,VAR=,VARn=);
/*===================================================================
作者:JIMMY
最後修改:20190712
DS:輸入要檢查的DATASET
VAR:輸入該DATASET 內要檢查的所有文字變數      | 分隔   如: X | Y
VARn:輸入了多少個VAR
此程式會對DS的VAR進行COMPRESSKEEP英文與數字，然後與COMPRESSKEEP前的DS比對，如壓縮前後不一樣則存到WORK.VAR(變數名稱)
===================================================================*/

%do i = 1 %to  &VARn.; 
    %let VAR&i. = %qscan(%bquote(&VAR.),&i.,%str(|)); 
 data tmp_DS;
  set &DS.;
   &&VAR&i.. = compress(&&VAR&i.. ,'1234567890',"kfs");
    n=_n_;
 run;
 
 data &&VAR&i..  (keep= &&VAR&i..   compressGarbled   n);
  merge &DS.   tmp_DS  (rename=(&&VAR&i.. = compressGarbled));
  if  &&VAR&i..^=compressGarbled;
 run;

 %let open_d = %sysfunc (open( &&VAR&i.. )); 
 %let tmpds = %sysfunc(attrn(&open_d., nobs));
 %let close_d = %sysfunc(close(&open_d.));
 %if &tmpds. < 1 %then %do;
  proc datasets lib=work  mtype=(data view) nolist nowarn;
  delete &&VAR&i.. ;
  run;quit;
 %end;

%end;
proc datasets lib=work  mtype=(data view) nolist nowarn;
delete tmp_DS;
run;quit;

%mend;
%Garbled(DS=test,VAR=X | Y, VARn=2)

例子:
建立一個有亂碼的DATASET
X長度只有1，而DATALINES內有中文的話勢必會變成亂碼
data test ;
length x $ 1.  ;
input X $ ;
Y= 'A';
datalines;
a
b
1
測
;
run;
%Garbled(DS=test,VAR=X | Y, VARn=2)

執行後，會產生以變數名稱命名的WORK DATASET，如該變數無亂碼則該DATASET不產生(被刪除)。

SAS DATASET 透過 ODS 產生EXCEL

ods _all_ close;
ods excel file="E:\jimmy\excel.xlsx"   options (sheet_name="sheet1") STYLE= HTMLBLUE ;
proc print data=MST_PREP.ACCOUNT_TRANSACTIONS;
run;
ods excel options(sheet_name="sheet2");
proc print data=MST_PREP.ACCOUNT_TRANSACTIONS;
run;
ods excel close;

紅框處視需求改寫成MACRO變數，就可以自動OUTPUT了。
STYLE= 可參考下圖改變OUTPUT的格式。
ODS OUTPUT 也可參考另外一篇(連結)。

偵測多個SAS LOG狀態

前半段MACRO來源:识别文件夹下指定文件类型及名称

將所有偵測LOG後的結果存入DB，

目前設定:

一、執行它時，[SAS_JOB_log]會先清空，會抓
"F:\SASWork\SAS\Config\Lev1\SASApp\BatchServer\Logs\DB_LOG\"下的LOG檔名稱包含"AGP.%" "C2C.%" "L2C.%" "LCK.%" '%VA_Report%'  '%WATCH%'的LOG，會先抓取今天日期的LOG，如果沒抓到就會抓前一天的LOG，最多到前天。

二、那些LOG要寫入DB

會先判斷ERROR_n1, ERROR_n2, ERROR_n2。

n1:該行出現 "ERROR:" 且不出現"MPRINT" 與 '%put ERROR:'。

n2: 該行出現了"ERROR: SAS ended due to errors" 或是 "ERROR: Errors        printed on page"

n3:該行出現了NOTE: The SAS System used

三、寫到資料庫[SAS_JOB_log]的欄位[log] ,[job_nm] ,[status] ,[date]

[log]:為LOG檔該行的LOG訊息。

[job_nm]:LOG檔的完整名稱。

[date]:執行當下的日期。

[status]:如最下面三張圖

如果該JOB除現ERROR: status就會記錄ABORT， 如果LOG訊息有多行在DB就會記錄多行如圖: (從n1-8到n2行寫入DB，如果沒有n2就寫入n1上下8行)

結果:

AGP.01.battest_07MAR19134632.2190001.log

[SAS_JOB_log]

設定LOG路徑
與需要納入的LOG檔案名稱

建立一個暫存的資料夾存放LOG，因為可能有正在執行中的SAS程式，
其LOG會鎖住，不能讀取。
所以將全部LOG複製新的一份到暫存進行偵測。

最後再把所有暫存的LOG刪除。

 %macro sas_job_log();
%MACRO FIND_SAS(DIRNAME,TYPE);/*参数有两个：路径，文件类型后缀*/
LIBNAME SASJOB "&SASJOB_PATH";
%PUT %STR(----------->DIRNAME=&DIRNAME);
%PUT %STR(----------->TYPE=&TYPE);
DATA SASJOB.DIRFILES; 
RC=FILENAME("DIR","&DIRNAME");/*把&DIRNAME值传给文件引用符“DIR"*/ 
OPENFILE=DOPEN("DIR");/*得到路径标示符OPENFILE，DOPEN是打开directory的sas内置函数*/
IF OPENFILE>0 THEN DO;/*如果OPENFILE>0表示正确打开路径*/ 
NUMMEM=DNUM(OPENFILE);/*得到路径标示符OPENFILE中member的个数nummem*/ 
DO II=1 TO NUMMEM; 
NAME=DREAD(OPENFILE,II);/*用DREAD依次读取每个文件的名字到NAME*/

FILEPATH="&DIRNAME"||NAME;
fnum=COMPRESS(NAME,".xml","");
fnum=COMPRESS(fnum,"user_case","");

OUTPUT;/*依次输出*/
END;
END;
KEEP NAME filepath fnum;/*只保留filepath、fnum列*/
RUN;

PROC SORT DATA=SASJOB.DIRFILES;/*按照NAME排序*/
BY DESCENDING NAME;
%IF &TYPE^=ALL %THEN %DO;/*是否过滤特定的文件类型&TYPE*/ 
WHERE INDEX(UPCASE(NAME),UPCASE(".&TYPE"));/*Y,则通过检索NAME是否包含&TYPE*/
%END;
RUN;

/*PROC PRINT DATA=SASJOB.DIRFILES;*/
/*RUN;*/

%MEND FIND_SAS;

%MACRO DIR(SASJOB_PATH,TYPE,DATASET_NUM);
LIBNAME SASJOB "&SASJOB_PATH";
%LET SASJOB_PATH=&SASJOB_PATH.;
%FIND_SAS(&SASJOB_PATH,&TYPE);

DATA TEMP(KEEP=NAME_SAS NAME_LOG);
SET SASJOB.DIRFILES;
NAME_SAS=COMPRESS(NAME);
NAME_LOG=COMPRESS(TRANWRD(NAME,'.sas','.log'),);
IF NAME_SAS ^='dirfiles.sas7bdat' AND NAME_SAS ^='dirfiles.sas7bdat.lck';



RUN;
PROC SORT DATA=TEMP OUT=SASJOB.DIRFILES;
BY NAME_SAS;
RUN;

data _null_;
 td_p  =  today();
 td_1_p=today()-1;
 td = put(td_p, date7.);
 td_1 = put(td_1_p, date7.);
 call symputx("td",td);
 call symputx("td_1",td_1);
run;

data SASJOB.DIRFILES;
 set SASJOB.DIRFILES;
      x_obs=1;
 if  find(NAME_LOG , "&td.") >0    then do;
  date = input("&td.", date9.);
  Real_job_nm= SUBSTR(NAME_SAS,1, 14);
  output;
 end;
/*    前1天*/
 if  find(NAME_LOG , "&td_1.") >0  then do;
  date = input("&td_1.", date9.);
    Real_job_nm= SUBSTR(NAME_SAS,1, 14);
  output;
 end;
                  
run;

data tmp_X_obs;
 x_obs = 1;
run;
proc append base = SASJOB.DIRFILES   data =tmp_X_obs;
run;

%let open_d = %sysfunc (open(SASJOB.DIRFILES)); 
%let DIRFILES_n = %sysfunc(attrn(&open_d., nobs));
%let close_d = %sysfunc(close(&open_d.));

%if  &DIRFILES_n. =1 %then %do;
 data SASJOB.DIRFILES;
  set TEMP;
  where  NAME_LOG  ?  "&td_1."  
                    and  (NAME_LOG like "AGP.%"  or   NAME_LOG like "C2C.%"
         or   NAME_LOG like "L2C.%"  or   NAME_LOG like "LCK.%"   
      or  NAME_LOG  like '%VA_Report%'  or   NAME_LOG like '%WATCH%');
 run;
%end;
 
 
%MEND DIR;
%DIR(D:\SAS\Config\Lev1\SASApp\BatchServer\Logs,log,0);


%x_fcf_twn_ini(log=T,ea=Y);
%fcf_get_runasofdate;

 
proc sql noprint;
 select distinct job_nm 
 into : lag_job_nm  separated by "|" 
 from sasjob.tmp_lag_log
 where status in("NO CONNECTION", "ABORT");

 create table tmp_lag_job as
 select distinct job_nm
 from sasjob.tmp_lag_log
 where status in("NO CONNECTION", "ABORT");
quit;


/*%put &lag_job_nm.;   出ERROR的只要一次*/
%let open_d = %sysfunc (open(tmp_lag_job)); 
%let LAG_JOB_OBS = %sysfunc(attrn(&open_d., nobs));
%let close_d = %sysfunc(close(&open_d.));

%do L= 1 %to &LAG_JOB_OBS.;
  %let lag_job_nm&L. = %qscan(%bquote(&lag_job_nm.),&L.,%str(|)); 
 data sasjob.DIRFILES;
  set sasjob.DIRFILES;
  where  NAME_LOG ^= "&&lag_job_nm&L..";
 run;
%end;
 

data tmp_log;
 if 0 then set  seg_kc.SAS_JOB_log;
run;
 
proc datasets library=seg_kc noprint;
 delete SAS_JOB_log ;
run;

data seg_kc.SAS_JOB_log  ;
 if 0 then set tmp_log;
 if  job_nm ^= "";
run;

proc datasets library=work noprint;
 delete tmp_log;
run;





 
proc sql  noprint;
 create table tmp_job_n as
 select NAME_LOG as my_logtime
 from sasjob.DIRFILES
 where (NAME_LOG like "AGP.%"  or   NAME_LOG like "C2C.%"
        or   NAME_LOG like "L2C.%"  or   NAME_LOG like "LCK.%"   
     or  NAME_LOG  like '%VA_Report%'  or   NAME_LOG like '%WATCH%');

 select my_logtime
 into : my_logtime  separated by "\"
 from tmp_job_n;
quit;
%let open_d = %sysfunc (open(work.tmp_job_n)); 
%let job_n = %sysfunc(attrn(&open_d., nobs));
%let close_d = %sysfunc(close(&open_d.));




/*%put &my_logtime.;*/


/*%let etls_jobName=LCK.02.SOURCE_CHECK;*/
/*%let t=1;*/
/**/
/*%let etls_jobName = AGP.05.CDD_MAIN_PROCESS;*/
/*%let t=14JAN19143028.3110001;*/
/**/
/**/
/*%let etls_jobName=RERUNAGP;*/
/*%let t=1;*/
/**/
/**/
/*%put &etls_jobName._&t..log;*/
/* */

x 'md "F:\SASWork\SAS\Config\Lev1\SASApp\BatchServer\Logs\DB_LOG\"  ';
x 'xcopy  "D:\SAS\Config\Lev1\SASApp\BatchServer\Logs\*.*"      "F:\SASWork\SAS\Config\Lev1\SASApp\BatchServer\Logs\DB_LOG "/Y'   ;

%do  jon_num = 1  %to  &job_n.;

 %let jon_num&jon_num. = %scan(&my_logtime., &jon_num.,%str(\));

 data SAS_JOB_log_tmp1 ;
  infile "F:\SASWork\SAS\Config\Lev1\SASApp\BatchServer\Logs\DB_LOG\&&jon_num&jon_num.."  dsd missover dlm='09'x;
  length log1 log2 log3 $3000.;
  input log1 $  log2 $;
  retain error_n   error_n2 error_n3;
  if find(log1, "The SAS System          20") =0 ;
  if find(log1, "Create Time=20") =0 ;
  if missing(log1)=0; 
  

        if  find(log1, "ERROR:")  >0  and  find(log1, "MPRINT")=0    and find(log1, '%put ERROR:')=0       then  do ;
        log3 = log1;
           error_n=_n_;
    output;
   end;

         if    find(log1, "ERROR: SAS ended due to errors")>0   or   find(log1, "ERROR: Errors printed on page") >0     then  do ;
        log3 = log1;
           error_n2=_n_;
    output;
   end; 

    if   find(log1, "NOTE: The SAS System used:")>0 then do;
         error_n3=_n_;
      output;
    end;

 run;


%let open_d = %sysfunc (open(work.SAS_JOB_log_tmp1)); 
%let SASLOG_obs = %sysfunc(attrn(&open_d., nobs));
%let close_d = %sysfunc(close(&open_d.));
%if &SASLOG_obs. > 0 %then %do;
 data _null_;
  set SAS_JOB_log_tmp1;

  if  _n_ =  1 then do ;
    call symputx("error_n",error_n) ;
  end;

  call symputx("error_n2",error_n2);
   call symputx("error_n3",error_n3);
/*    %put  &error_n.  &error_n2.    &error_n3.;*/
 run;
 

 data SAS_JOB_log_TMP2 (  drop=log1);
  infile "F:\SASWork\SAS\Config\Lev1\SASApp\BatchServer\Logs\DB_LOG\&&jon_num&jon_num.."  dsd missover dlm='09'x;
  length log1  log  $3000.   job_nm $80.;
  input log1 $  log $ job_nm$;
  job_nm= "&&jon_num&jon_num..";
  date = input(put(&runasofdate.,8.),yymmdd8.) ;
  if find(log1, "The SAS System          20") =0;
     if find(log1, "Create Time=20") =0 ;
    if missing(log1)=0; 
  format  date yymmdd8.;
/*     if  find(log1, "NOTE: The SAS System used:")  >0   and     find(log1, "ERROR:")  >0    then  do;*/
/*    if  find(log1, 'MPRINT(SVCREG):   put "ERROR:')  =0  or   find(log1, "MPRINT(SVCREG):   put 'ERROR:")  =0 then do;*/
/*     log3="RUNOK"; */
/*     output;*/
/*    end;*/
/*  end;*/
if &error_n2.^=. then do;                  
  if  &error_n2.  >= _n_       >= &error_n. - 8    then do;              
       log = log1;
   if (find(log,"關閉一個現存的連線") >0  or find(log,"無法開啟 SQL Server 連線") >0 )   and
        (find(job_nm, "AGP.01.I")>0     or  find(job_nm, "AGP.03.I")>0     or  find(job_nm, "LCK")>0      or   find(job_nm, "L2C")>0)  
                   then do;
     status = "NO CONNECTION";
     output;
   end; else do;
      status = "ABORT";
      output;
     end;
  end;
end;  else do;
  if  &error_n.+8 >= _n_       >= &error_n. - 8   then do;        
       log = log1;
   if find(log,"關閉一個現存的連線") >0  or find(log,"無法開啟 SQL Server 連線") >0      and
         (find(job_nm, "AGP.01.I")>0     or  find(job_nm, "AGP.03.I")>0     or  find(job_nm, "LCK")>0      or   find(job_nm, "L2C")>0)  
               then do;
     status = "NO CONNECTION";
     output;
   end; else do;       
              status = "ABORT";
               output;
      end;
  end;
end;




 if _n_ = &error_n3.; 
   log = "RUNOK";
   status = "RUNOK";
   output;


run;
 %end; %else  %do;
 data SAS_JOB_log_TMP2 ;
   length job_nm $80.  ;
  log = 'RUNNING';
  status = 'RUNNING';
  job_nm= "&&jon_num&jon_num..";
  date = input(put(&runasofdate.,8.),yymmdd8.) ;

  format  date yymmdd8.;
 run;
 %end;


 %let open_d = %sysfunc (open(work.SAS_JOB_log_TMP2)); 
%let noc = %sysfunc(attrn(&open_d., nobs));
%let close_d = %sysfunc(close(&open_d.));


data SAS_JOB_log_TMP2 ;
 set SAS_JOB_log_TMP2;
 XN = _N_;
 do _n_ = 1 to  &noc.;
  if  lag(status) = "NO CONNECTION"   then  status = "NO CONNECTION";
  if  lag(status) = "ABORT"   then  status = "ABORT";
 end;
 if log = "RUNOK" and (status= "ABORT"  or  status = "NO CONNECTION") then delete;
run;


 proc sort data=SAS_JOB_log_TMP2;by descending status  ;run;

data SAS_JOB_log_TMP2 ;
 set SAS_JOB_log_TMP2;
 do _n_ = 1 to  &noc.;
  if  lag(status) = "NO CONNECTION"   then  status = "NO CONNECTION";
  if  lag(status) = "ABORT"   then  status = "ABORT";
 end;
run;
 proc sort data=SAS_JOB_log_TMP2;by   XN  ;run;





 proc append base=sasjob.tmp_lag_log  data=SAS_JOB_log_TMP2  FORCE;
 run;
 proc append base=seg_kc.SAS_JOB_log  data=SAS_JOB_log_TMP2  FORCE;
 run;
%end;

proc sort data=sasjob.tmp_lag_log nodupkey;
by job_nm;
run;
 


x ' rd "F:\SASWork\SAS\Config\Lev1\SASApp\BatchServer\Logs\DB_LOG\" /s /q';
%mend;

%sas_job_log()

比較兩個 SAS DATA 變數的屬性 MACRO

先將兩個SAS DATA的屬性output到sit_var與uat_var，把欄位屬性變成觀測值。
如下:

data DB1_DS1;
length y $ 13.;
input x y z;
format x ddmmyy.;
datalines;
1 1 1
;
run;

data DB2_DS1;
length y $ 26.;
input x y z;
format x date9.;
datalines;
1 1 1
;
run;

ods trace on / listing;
 proc contents data=DB1_DS1;
ods output Variables= sit_var;
run;
ods trace off;

ods trace on / listing;
 proc contents data=DB2_DS1;
ods output Variables= uat_var;
run;
ods trace off;

在執行 %CK_VALUE 或  proc compare

/*====================================================
DATA1     DB_A 資料集1    work.也要輸入
DATA2    DB_B 資料集2
BY  KEY
ALL_VAR 全部變數名稱 | 分隔
VAR_NUM 幾個變數
JIMMY CHEN
======================================================*/
%macro CK_VALUE(DB1_DATA=,
   DB2_DATA=,
   BY =,
   ALL_VAR = ,
   VAR_NUM=);
%let DATA1 = %scan(&DB1_DATA., 2,%str(.));
%let DATA2 = %scan(&DB2_DATA., 2,%str(.));

proc sort data = &DB1_DATA. out = DB1_REN nodupkey; by &BY.;run;
proc sort data = &DB2_DATA. out = DB2_REN nodupkey; by &BY.;run;

%do d = 1 %to 2;
 %do VAR_N = 1 %to &VAR_NUM.;
  %let VAR&VAR_N. = %scan(&ALL_VAR., &VAR_N.,%str(|));
/*  %put  &&VAR&VAR_N.. ;*/
/*  %put  &&VAR&VAR_N.._&&DATA&D..;*/
  data DB&d._REN;
   length &&VAR&VAR_N.._ck $30.;
   set DB&d._REN(rename =(&&VAR&VAR_N.. =&&VAR&VAR_N.._DB&d.));
  run;
 %end;
%end;                                  

%do VAR_N = 1 %to &VAR_NUM.;
 %let VAR&VAR_N. = %scan(&ALL_VAR., &VAR_N.,%str(|));
 data CK_&&VAR&VAR_N.. (keep =&BY. &&VAR&VAR_N.._ck);
  merge DB1_REN
     DB2_REN;
  by &BY.;
  if  &&VAR&VAR_N.._DB1 =  &&VAR&VAR_N.._DB2 then &&VAR&VAR_N.._ck = "OK";
   else  &&VAR&VAR_N.._ck = "NG";
  if missing(&&VAR&VAR_N.._DB1) then &&VAR&VAR_N.._ck = "NOT FOUND &DB1_DATA.";
  if missing(&&VAR&VAR_N.._DB2) then &&VAR&VAR_N.._ck = "NOT FOUND &DB2_DATA.";
  if missing(&&VAR&VAR_N.._DB1)  and  missing(&&VAR&VAR_N.._DB2)  then &&VAR&VAR_N.._ck = "NOT FOUND"; 

 run;
%end;


data CK_VALUE_ALL;
  merge DB1_REN
      DB2_REN;
  by &BY.;
run;

%do VAR_N = 1 %to &VAR_NUM.;
 %let VAR&VAR_N. = %scan(&ALL_VAR., &VAR_N.,%str(|));
 data  CK_VALUE_ALL;
  merge  CK_VALUE_ALL
     CK_&&VAR&VAR_N..;
    by &BY.;
 run;

 proc freq data = CK_VALUE_ALL;
  title "檢核觀測值: &&VAR&VAR_N..";
  table &&VAR&VAR_N.._ck;
 run;title;

proc print data = Ck_value_all;
 where &&VAR&VAR_N.._ck = "NG";
run;
%end;

%mend;

              
 

%CK_VALUE(DB1_DATA=work.sit_var,
  DB2_DATA=work.uat_var,
  BY=Variable,
  ALL_VAR=Type | Len| Format,
VAR_NUM=3)


結果: