Axio Research Idea to Application via SAS Macro

Axio Research Idea to Application via SAS Macro Language Reading Directories Bill Coar (bill. coar@gmail. com)

Outline • • • The idea Translate the idea to SAS code A couple applications Extension of idea to information about dataset Final remarks

The Idea • Obtain information about files in a directory • Generate a list • Apply some command to each element (sequentially) – For example: run each one in batch mode, or search log files

The Idea • One way to Implement – Generate scripts to be executed from a command prompt – May or may not be so simple • Requires additional programming knowledge as well as knowledge of operating environment • A different approach – From within SAS

The Idea • From within SAS using macro language – Open a directory – Read in some information – Close a directory • Additionally, – Initiate a list and counter • Increment when information read in meets some condition(s)

SAS Functions for the Idea • Sysfunc – %Sysfunc(function(argument(s))<, format>); • Dopen(fileref) – Opens a directory and returns a directory identifier value • Dnum(directory-id) – Returns the number of members in a directory Source: SAS Help and Documentation

SAS Functions for the Idea • Dread(directory-id, member-num) – Returns the name of a directory member • Dclose(directory-id) – Closes a directory that was opened by the DOPEN function • Others to gain information about data – Open, Close, Attrn Source: SAS Help and Documentation

Open the Directory • Specify a directory %let DLOC=D: clientprojectprograms; • Define a fileref %let filrf=MYDIR; %let rc=%SYSFUNC(filename(filrf, &DLOC)); • Open the directory based on the fileref %let DID=%SYSFUNC(dopen(&FILRF)); • See how many members there are %let NUMF=%SYSFUNC(dnum(&DID));

Read information/increment lists %let FNUM=0; %do F=1 %to &NUMF; %let thisread=%upcase(%sysfunc(dread(&DID, &F))); /* insert some condition for incrementing to a list */ %if %scan(&THISREAD, 2)=SAS %then %do; %let fnum=%eval(&FNUM+1); %let dat&FNUM=%scan(&THISREAD, 1); %end;

Close the directory %LET rc=%SYSFUNC(dclose(&DID));

The condition – Including Files • Pre-specify some sort of wildcard (*) %macro doit(…, inf=, …); • Use the %index function %if %index(&INF, *) gt 0 %then %let INFILE=%SUBSTR(&INF, 1, %LENGTH(&INF)-1); %else %let INFILE=&INF; %if %scan(&THISREAD, 2)=SAS & %index(&THISREAD, &INFILE) gt 0 %then %do; %let fnum=%eval(&FNUM+1); %let dat&FNUM=%scan(&THISREAD, 1); %end;

The condition – Excluding Files • Specify a list of file names to exclude • Use the /minoperator (SAS version 9. 2) %macro doit(…, excf=, …) /minoperator; %if %scan(&THISREAD, 2)=SAS %then %do; %if %scan(&THISREAD, 1) in &EXCF %then %do; * Leave this blank to take advantage of the minoperator. ; %end; %else %do; %let fnum=%eval(&FNUM+1); %let dat&FNUM=%scan(&THISREAD, 1); %end;

Applications • SAS batch • Searching logs • An Application with data – Assessing data values for each variable

Running SAS batch • Recall the idea: we are doing this from within SAS • Execute SAS with appropriate SAS system options • Need to identify where the programs reside and where to direct the output – Redirect to an output folder using system options (no proc printto)

Running SAS batch – Sample Code OPTIONS NOXWAIT XSYNC; %do a=1 %to &FNUM; * Needed to specify some SAS system options; %let thisfile=%CMPRES(&FILEDIR. &&DAT&A. . . SAS); %let atoexec='%CMPRES(&FILEDIR. AUTOEXEC. SAS)'; * Specify the actual system command; %let scom=%cmpres(C: PROGRA~1SASFOU~29. 2sas. exe -nodlgboxes -autoexec &atoexec –nosplash –icon -sysin &&DAT&A ); * Execute the command; data _null_; call system("&scom"); run; %end;

Searching Log Files • Desire to search the log file(s) – Done through data _null_ – Loop through each file and search each for some text using INDEX • UNINITIAL, ERROR: , STOPPED, WARNING, MERGE STATEMENT – Output results to a single text file

Searching Log Files – Sample Code data _null_; * Specify the input for this iteration and output file. ; infile "&THISFILE" end = eof length = a sharebuffers; file "&LOGFL" %if &A > 1 %then mod; ; * Read in each line of text from a log file. input rline $varying 200. a; retain linnum errnum; * Added SAS messages and look for errors; if _n_ = 1 then do; linnum=0; errnum=0; put @1 "FILENAME SEARCHED: &THISFILE"; put @1 "TERMS SEARCHED: UNINITIAL, ERROR: , STOPPED, WARNING, MERGE STATEMENT"; end; …more SAS code using INDEX command put statements Run;

Examples • Run a single program that generates a figure %runbatch(f_elft); %logsrch(f_elft); • Run all the programs that generate data and search the log files %runbatch(a_*); %logsrch(a_*);

Extend to Data • Rather than working with a directory, work with a dataset, for example: – Determine lists of numeric and character variables – Identify formats (date variables) • Obtain summary statistics about each variable

Extend to Data • Open a dataset and initialize lists %let ds=%sysfunc(open(&SASin, i)); %let columns=%sysfunc(attrn(&DS, NVARS)); %let nc=0; %let nn=0; %let nlist=; %let clist=;

Extend to Data • Loop through and obtain info about each variable %do i=1 %to &columns; %let col&i=%sysfunc(varname(&ds, &i)); %let ctype&i=%qsysfunc(vartype(&ds, &i)); %if &&ctype&i=C %then %do; %let nc=%eval(&nc+1); %let clist=&clist &&col&i; %end; %else %do; %let nn=%eval(&nn+1); %let nlist=&nlist &&col&i; %end;

Extend to Data • Close the dataset %let rc=%sysfunc(close(&DS));

Application within Data • For numeric variables – Mean, min, max, percent missing • For character variables – Lengths and percent missing • For dates (and times) – Min, max, percent missing

Conclusions • • Idea is fairly straight-forward Plenty of applications for the idea Not restricted to clinical research Certainly are alternatives – Information from Proc Contents – Same functions used within a dataset

Conclusions Any Question?