4月 2019 ~ Jimmy Chen ~SAS

前半段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()

Share This: Facebook Twitter Google+ Stumble Digg

Jimmy Chen

Jimmy Chen ~SAS

SAS Macro

2019年4月9日星期二

偵測多個SAS LOG狀態

Translate

最近30天流量

關於我自己

Popular Posts

網頁連結

SAS CERTIFIED BASEPROGRAMMER

SAS Certified Advanced Programmer