SAS File functions can help you leave an audit trail

1. SAS File functions can help you leave an audit trail Subhash Mantha SAS Developer subhashmantha@gmail.com

2. Audit trail • Important in the following fields • Clinical Trials • Financial Companies • Product Development • Answers four important questions • When • Why • How • Who

3. Some of the parameters used for Trail Capturing • Date • Time • Who ran the program • Run time of the program

4. Methods for capturing a trail • SAS Logs • Excel Files • XML files • SAS Datasets • CSV/Text Files

5. SAS Way of capturing a trail • Datasets • Append • Modify • Recreate • External files • Append • Recreate

6. Trail by datasets • Data step • Set • Modify • Proc step • Append

7. Trail by external files • Data step • File (recreate) • File with mod option (append) • Proc step • Proc export (Recreate /add new sheets to an existing file) • ODS CSVALL

8. Writing to external files • Data step • Proc step • SAS File functions

9. SAS File functions • Filename: Assigns a file reference in a data step • Fexist: Checks if a file exists. • Fopen : opens the file and creates a file handler • Fclose : Closes the file that has been opened using the file handler • Fput : Writes the information to the file data buffer • Fwrite : Writes data from file data buffer to the actual file

10. Appending information to the bottom of a file Data step: Data _null_; File <fileref> <file options> mod; Set somedsname; Put fields we want; Run; File functions: Data _null_; Rc_open=filename(‘fileref’, “full file name along with path”); if rc_open=0 and fexist(‘fileref’) then do; fid=fopen(‘fileref’ , ’a’); putrc=fput(fid,<string to be written to the file>); writerc=fwrite(fid); closerc=fclose(fid); End; Run;

11. Advantages of using file functions • Verify if the file exists • Check if the file is in use • Wait until the file is available to use • Catch exceptions if data write to a file failed • Help work around user locks

12. Concurrent access of datasets • Found in multi user systems • Several people trying to access the same file • Others might have a file in read mode while somebody else is trying to update the file • Methods to address concurrent access : • “Assume” That it never happens as in a single user system • Try to resolve the lock

13. Code %macro update_audit_file (name_of_program=, campaign_channel=, result_of_run=&syscc._&sysrc._&sysmsg, name_of_audit_file=); %local user date_of_run ; %let user=%sysget(USERNAME); %let date_of_run=%sysfunc(putn(%sysfunc(datetime()),datetime18.)); %let filerf=update; proc sql noprint; select distinct scan(xpath,-1,'\') into :files separated by '|' from sashelp.vextfl where index(xpath,'.sas')>0; quit; %let rc=%sysfunc(filename(filerf,"&name_of_audit_file")); %put rc=&rc; %if %sysfunc(fexist(&filerf)) %then %do; %let open_rc=%sysfunc(fopen(&filerf,a)); %put &open_rc=; %do %while (&open_rc <= 0 ); %let rc_sleep=%sysfunc(sleep(10)); %put The file being updated is open please close it; %put SAS will try to update it in 10 seconds; %let open_rc=%sysfunc(fopen(&filerf,a)); %end;

14. Code Continued…. %if &open_rc > 0 %then %do; %let log=%sysfunc(getoption(altlog)); %let log_file=%sysfunc(scan(%str(&log),-1,'\')); %let list=%sysfunc(getoption(altprint)); %let list_file=%sysfunc(scan(%str(&list),-1,'\')); %let path=%sysfunc(getoption(sasinitialfolder)); %let put_rc=%sysfunc(fput(&open_rc,%str(&user,&date_of_run,&name_of_campaign,&campaign_channel,&path,&result_of_run,&log_file,&list_file,&files))); %let write_rc=%sysfunc(fwrite(&open_rc)); %let close_rc=%sysfunc(fclose(&open_rc)); %end; %put &open_rc &close_rc; %end; %else %do; %put the file &filerf does not exist; %put creating file &name_of_audit_file; data _null_; file "&name_of_audit_file"; put 'user,dateofrun,name_of_campaign,campaign_channel,path,result_of_run,log_file,list_file,files'; %put %sysfunc(sysmsg()); %end; %mend update_audit_file;

15. References • SAS on-line documentation in Release 9.1.3 • Using SAS Functions in Data Steps, Yue Ye, The R.W. Johnson Pharmaceutical Research Institute, Raritan, NJ Yong Lin, The Cancer Institute of New Jersey, New Brunswick, NJ • Acknowledgements • Vitaly Feldman, SAS Institute Inc.