SAS Macros to Search and Report Errors and Warnings from your SAS Logs

How many a times at work have you gone thru the logs looking for ERROR, WARNINGS, NOTES etc for more information about the generated problem?…I’ve been doing a lot like that lately for developing / debugging / lines of code for more information…I just wished I had a piece of code that would search and report me the location / line number and few more information about….luckily I found this macro in a SAS SUGI paper…here’s the link….www2.sas.com/proceedings/sugi25/25/po/25p219.pdf

Then I had to customize it a little more to make the macro browse thru a folder of log files with a common runtime so that I can get all the errors into one report file…

Here’s an example and the full code below….
I intentionally induced couple of errors highlighted in RED font locations in these datasteps below….

%let LogLoc=C:\SASDATA;

%let cyle_runtime=10OCT2010102001;

proc printto log="&LogLoc.\example1_&cyle_runtime..log"; run;

DATA example1;

INPUT ID $ 1 SBP 2-4 DBP 5-7 GENDER $ 8 AGE 9-10 WT 11-13;

DATALINES;

1120 80M15115

2130 70F25180

3140100M89170

4120 80F30150

5125 80F20110

;

RUN;

PROC MENS;

RUN;

proc printto; run;

proc printto log="&LogLoc.\example2_&cyle_runtime..log"; run;

DATA CDS2;

INPUT @1 CATEGORY $9. @10 NUMBER 3.;

DATALINES;

JAZZ     252

POP       49

CLASSICAL 59

RAP       21

GOSPEL    44

JAZZ      21

;

run;

ODS RTF;

PROC FREQ DATA=CDS ORDER=FREQ; WEIGHT NUMBER;

TITLE3 'READ IN SUMMARIZED DATA';

TABLES CATEGORY;

RUN;

proc printto; run;

This code produces 2 log files at C:\SASDATA\ with timestamp on them..

Now running my SAS Macro %ReportErrorWarnings with the right parameters would result in a Error file at the same location…

%ReportErrorsWarnings(searchstr,searchloc,searchfiles,outfile,lines2display);

The macro from the SUGI paper was good…it created an arrow against the error / filename and linenumber…I customized the macro to browse thru a folder / location of logs etc… I agree it isn’t pretty but it works for me….This error file gives me the exact location (for eg line 20 in example 1 file and 2 lines above and below….). The number of lines to display above and below is again customizable though as the last parameter…

Similarly one can search for “ERROR:”,”WARNING:”, “NOTE:”,”not resolved”, “uninitialized”, “invalid data”, “_ERROR_=1″, “missing values”, “lock held by” etc…any other usual errors that you might want to track.

%ReportErrorsWarnings(searchstr,searchloc,searchfiles,outfile,lines2display);

Here’s the full code…with the repeatition of the above code again…for context…

You can get it from here as well…
http://snipplr.com/view/42133/sas-macros-to-search-and-report-errors-and-warnings-from-your-sas-logs/
or
http://www.docstoc.com/docs/57181769/SAS-Macros-to-Search-and-Report-Errors-and-Warnings-from-your-SAS-Logs

%macro recsinds(table,macvar);

/*----------------------------------------------------------------------

this macro can be used to get the count of observations in a dataset

into a macrovariable that you give.

usage: %recsinds(sashelp.class,macvar);

&macvar is the observation count of your dataset

----------------------------------------------------------------------*/

%global &macvar;

%local handle rc;

%let handle = %sysfunc(open(&table)); /* open the table */

%if &handle %then

%do;

/* get the observation count into the macvar macro variable */

%let &macvar = %sysfunc(attrn(&handle, nlobs));

%let rc = %sysfunc(close(&handle)); /* close the dataset */

%end;

/* write the record count to the log */

%put recsinds &table: &&&macvar.    &macvar=&&&macvar.;

%mend recsinds;

%macro SearchErrorsWarnings(strg,loc,dsx,mm=1);

* mm=# of lines to display before and after target lines. mm=0,1,2 ...etc.;

filename dd "&loc.\&dsx.";

%global nnn;  *nnn=# of records in dsx;

data aa (keep= filen arrow linum xx);

infile dd length=ln end=last;

length llprted pthru 8 arrow $3 xx $120 filen $400;

retain llprted pthru 0;

*LLPRTed:line # of Last Line Printed; * PThru: to be line # of the last of: * the 2mm+1 lines to be displayed;

length %do jj=1 %to &mm; x&jj %end; XT $ 120;

retain filen XT ' ' %do jj=1 %to &mm; x&jj %end;;;

filen="&dsx.";

* These store the mm lines that are; * to be printed prior to a Find; * Next save these mm lines;

%do jj=%eval(&mm-1) %to 1 %by -1;

x%eval(&jj+1)=x&jj;;

%end;

x1=XT;

input XT $varying120. ln;

XT=upcase(XT);

if index (XT,"THE SAS SYSTEM")>0 then delete;

if index (XT,upcase("&strg"))>0 then

do;

filen="========================================";linum=.; arrow='====='; xx='========================================================================================';

output;

filen="&dsx."; arrow='';

* Found one!;     * Insert a blank line between finds;      * provided the scopes of two finds ;    * do not overlap;

if (_n_ > pthru + &mm + 1) & pthru > 0

then

do;

filen="========================================";linum=.; arrow='====='; xx='========================================================================================';

output;

filen="&dsx."; arrow='';

end;

*Output mm lines preceding the find;

%do jj=&mm %to 1 %by -1;

if &jj<_n_ -="" code="" pthru="" then="">

do;

xx=x&jj;

linum=_n_ - &jj;

output;

end;

%end;

linum=_n_;

xx=XT;

if &mm > 0 then arrow="-->";

llprted=_n_;

output; * Output the Found line;

* Compute pthru, the line # of the ;            * last line of the scope so that the;        * next mm lines can be printed;

pthru=_n_+&mm;

arrow=' ';

end;

else

do;

if _n_<=pthru then

do;

linum=_n_;

xx=XT;

output;

* Outputting the next mm lines;

end;

end;

if last then call symput('nnn',left(put(_n_,5.)));

run;

proc append base=allout data=aa; run;

%mend SearchErrorsWarnings;

%macro ReportErrorsWarnings(searchstr,searchloc,searchfiles,outfile,lines2display);

proc sql;

drop table allout;

drop table aa;

drop table new;

quit;

%let dircmd0=%nrbquote(%str(dir /b /s %")%str(&searchloc.\&searchfiles.)%str(%"));

FILENAME rootloc pipe "&dircmd0";

/*data _null_; run;*/

data new;

length file $ 1000;

infile rootloc;

input file;

cnt=count(file,"\");

file=translate(file,' ','\');

file=scan(file,cnt+1,' ');

call symput(compress('filen'||_N_),file);

run;

%recsinds(new,nobs);

%if &nobs gt 0 %then %do;

%do k=1 %to &nobs;

%SearchErrorsWarnings(&searchstr.,&searchloc.,&&filen&k.,mm=&lines2display.);

%end;

data allout;

retain filen linum arrow xx;

set allout;

run;

data _null_;

file "&outfile." lrecl=32767;

set allout;

if _n_ =1 then

do;

put "FILENAME" '09'x "LINE #" '09'x "ARROW" '09'x "MESSAGE";

put "*****************************************************************************************************";

end;

put filen '09'x linum '09'x arrow '09'x xx '09'x;

run;

%end;

%else

%do;

%put ==================;

%put NO LOGS TO PROCESS;

%put ==================;

%end;

%mend ReportErrorsWarnings;

%let LogLoc=C:\SASDATA;

%let cyle_runtime=10OCT2010102001;

proc printto log="&LogLoc.\example1_&cyle_runtime..log"; run;

DATA example1;

INPUT ID $ 1 SBP 2-4 DBP 5-7 GENDER $ 8 AGE 9-10 WT 11-13;

DATALINES;

1120 80M15115

2130 70F25180

3140100M89170

4120 80F30150

5125 80F20110

;

RUN;

PROC MENS;

RUN;

proc printto; run;

proc printto log="&LogLoc.\example2_&cyle_runtime..log"; run;

DATA CDS2;

INPUT @1 CATEGORY $9. @10 NUMBER 3.;

DATALINES;

JAZZ     252

POP       49

CLASSICAL 59

RAP       21

GOSPEL    44

JAZZ      21

;

run;

ODS RTF;

PROC FREQ DATA=CDS ORDER=FREQ; WEIGHT NUMBER;

TITLE3 'READ IN SUMMARIZED DATA';

TABLES CATEGORY;

RUN;

proc printto; run;

%ReportErrorsWarnings(ERROR:,&LogLoc.,*_&cyle_runtime..log,&LogLoc.\error_&cyle_runtime..txt,2);

SAS Macro that lists the files at a particular directory on any FTP server (i.e. Windows Network Drive/Unix/Mainframe)...

Here's a macro that reads the filenames available at a particular directory on any FTP server (i.e. Windows Network Drive/Unix/Mainframe)...

For Windows network drives we use the Filename Pipe Statement

For Mainframe and Unix we use the FileName FTP protocol statement.

For further reference please refer to Filename statements in SAS Documentation.

First we need to create 2 Excel files

ServerList.xls – 3 columns with servertype | host | sourcedir

Ftpcredentials.xls – 3 columns host | user | pass

And then assign the physical location of these files to macrovariables fileloc and ftploc below….need to assign a name to the output dataset name to the outputdsn macro variable…

The output dataset will have the filenames | Fileattributes | builddt (i.e. timestamp)

So here’s the macro…Try it out for your self….

/*Define macro that counts obs in a SAS dataset*/

%macro RecsInDS(table,macvar);

%global &macvar;

%local handle rc;

%let handle = %sysfunc(open(&table));

%if &handle %then %do;

  %let &macvar = %sysfunc(attrn(&handle, nlobs));

  %let rc = %sysfunc(close(&handle));

%end;

%PUT RecsInDS &table: &&&macvar.    &macvar=&&&macvar.;

%mend RecsInDS;

/*Retrieve a username passwd for the Host */

%macro Ftpcredentials(ftploc,HOST);

%put;%put =============Macro: Ftpcredentials(&ftploc,&HOST);

%global user pass;      

%let user=; /* Initialize the Username */

%let pass=; /* Initialize the Password */

 data Ftpcredentials;

 length host1 $ 20;

  set UserPasswords;

  host1=compress("'"||host||"'");

  if host1="&HOST"; /*Proceed further only if the Host user/password combination is identified */

  call symput("user",compress("'"||user||"'"));

  call symput("pass",compress("'"||pass||"'"));

  run; 

%mend Ftpcredentials;

/*This Macro checks ALL the Server Locs and builds a SAS dataset with the filenames */

%macro ReadfilesfromDirectory(fileloc,ftploc,outputdsn);           

/* Read in the Server Locations Spreadsheet */

    PROC IMPORT OUT=ServerLocs

                DATAFILE= &fileloc. DBMS=EXCEL REPLACE;

                 SHEET="Sheet1$";

                 GETNAMES=YES;

                 MIXED=NO;

                 SCANTEXT=YES;

                 USEDATE=YES;

                 SCANTIME=YES;

            RUN;

/* Read in the FTP Credentials Spreadsheet */

            PROC IMPORT OUT=UserPasswords

                DATAFILE= &ftploc. DBMS=EXCEL REPLACE;

                 SHEET="Sheet1$";

                 GETNAMES=YES;

                 MIXED=NO;

                 SCANTEXT=YES;

                 USEDATE=YES;

                 SCANTIME=YES;

            RUN;

            /*Count of the number of Servers*/

            %RecsInDS(ServerLocs,num_ServerLocs2Process);

            /*create global variables for server locations */

            %do i=1 %to &num_ServerLocs2Process.;

               %global servertype&i. host&i. sourcedir&i.;;

            %end; /*close i loop;*/

            /* Create Macro variables for each of the server locations */

            data _null_; 

             set ServerLocs;

               count+1;

               cnt=put(count,4.);

                        /*MF PDS requires extra quotes for Directory eg. 'MainframeServer' */

               if servertype="MF" then call symput(compress("srcdir"||cnt),compress('"'||"'"||sourcedir||"'"||'"'));

               else if servertype="UX" then call symput(compress("srcdir"||cnt),compress("'"||sourcedir||"'"));

               else if servertype="WN" then call symput(compress("srcdir"||cnt),trim(left(sourcedir)));

               call symput(compress("servertype"||cnt),compress(servertype));             

               call symput(compress("host"||cnt),compress("'"||host||"'"));  

              run;

    /* set ftp_locs for Mainframe and Unix;*/

            %let ftp_locs=MF UX;

            /*Process this loop for every ServerLocation*/

            %do servcnt=1 %to &num_ServerLocs2Process;

              %if &&servertype&servcnt. eq WN %then

                 %do;

                                    %put;%put -------------SEARCHING FOR FILES ON SERVER LOCATION &&srcdir&servcnt.------------------------------;%put;

                                    %let dircmd1=%nrbquote(%str(dir /b /s %")%str(&&srcdir&servcnt)%str(%"));

                                    /* Use the Filename Pipe statement to return the listing of the directory */

                                    FILENAME rootloc pipe "&dircmd1"; 

                                    /*Execute the dir command and Put the results in a dataset filenames;*/

                                    data filenames;

                                                length filenm $ 200 cdir $ 800;

                                                infile rootloc truncover;

                                                input text $char1000.;

                                                filenm=scan(text,-1,'\');

                                                builddt=input(scan(text,1," "),MMDDYY10.);

                                                cdir=substr(text,1,find(text,filenm)-2);                            

                    run;          

                 %end;

                         /* if servertype is MF or UX */

              %else %if %index(&ftp_locs,&&servertype&servcnt.) gt 0 %then

                  %do; 

                %put;%put -------------SEARCHING FOR FILES ON SERVER: &&host&servcnt. AT LOCATION &&srcdir&servcnt.------------------------------;%put;

                                                %Ftpcredentials(&ftploc,&&host&servcnt); *get the username and password combo for the Host;

                                                 

                                                /* For every host location mentioned get the names of files that match our search str;*/

                                                /* Use the Filename FTP statement to return the listing of the directory */

                                                 FILENAME fname FTP "" LIST HOST=&&host&servcnt. CD=&&srcdir&servcnt. user=&user pass=&pass lrecl=32767 recfm=v debug;

                                                 /*Step 1*/

                                                 data &outputdsn;

                                                              length filenm $ 200; 

                                                             %if &&servertype&servcnt.=MF %then /* Mainframe based SRC server */

                                                              %do;

                                                               infile fname lrecl=256 pad firstobs=2; 

                                                               input @1  text $CHAR256.;  

                                                               /*put _infile_;*/

                                                               builddt=input(scan(text,3," "),YYMMDD10.);

                                                               filenm=scan(text,10," ");     

                                                              %end;

                                                              %else %if &&servertype&servcnt.=UX %then /* Unix based SRC server */

                                                              %do;

                                                                infile fname lrecl=256 pad firstobs=1; 

                                                                input @1  text $CHAR256.;

                                                                builddt=input(compress(scan(text,7," ")||scan(text,6," ")||year(today())),date9.);

                                                                filenm=scan(text,9," ");

                                                              %end;

                                                             format builddt date9.;

                                                run;

                  %end; /*end else if */

            %end; /*end servcnt loop*/

            /* Delete sensitive information from the disk */

            proc datasets lib=work nolist;

                        delete Userpasswords ServerLocs ftpcredentials;

            quit;

%mend ReadfilesfromDirectory;

/*--------------------EDIT THESE LOCATIONS ONLY -------------*/

/* Location of the Servers to Browse thru */

%let fileloc="C:\Documents and Settings\SASTechies\Desktop\ServerList.xls";

/* Location of the FTP usernames /passwords required to login to the servers mentioned above */

%let ftploc="C:\Documents and Settings\SASTechies\Desktop\ftpcredentials.xls";

/* Name of the output dataset */

%let outputdsn=filenames;

/*--------------------EDIT THESE LOCATIONS ONLY -------------*/

/* Call the Macro */

%ReadfilesfromDirectory(&fileloc,&ftploc,&outputdsn);