topic Re: sas looking for a physical file in SAS Procedures

sas looking for a physical file

deleted_user — Fri, 11 Sep 2009 18:13:13 GMT

I need to write a program in SAS that would look for a physical file in a folder and if it is available then look for the file name and look if it matches it with the file name in an excel sheet then write a value in excel sheet with a letter “e” .

How can I do it?

Re: sas looking for a physical file

sbb — Fri, 11 Sep 2009 20:11:23 GMT

For the "physical file detection", you can use the FILEEXIST call function, either in a DATA step or using %SYSFUNC and coding a macro. Then perform additional processing when you find the file.

Regarding the Excel question; suggest searching the SAS support http://support.sas.com/ website using either the site SEARCH facility or the Google advanced argument below:

update excel worksheet site:sas.com

Scott Barry
SBBWorks, Inc.

Re: sas looking for a physical file

Patrick — Sat, 12 Sep 2009 13:33:28 GMT

Hi
The excel workbook used in this example for windows is:"c:\SASUSER\test.xls".
The workbook has a sheet named "sheet1" with 2 column headings (A1: myfiles, B1: exist).
HTH
Patrick

/* read files in directory */
filename myfiles pipe 'dir "c:\SASUSER\*.sas" /B/ON';
data FilesInDir;
infile myfiles truncover;
input myfiles $50.;
put _infile_;
run;
filename myfiles clear;

/* Update Excel column 'exist' if matching filenames */
LIBNAME WrkBk EXCEL "c:\SASUSER\test.xls" scan_text=no;
proc sql;
update wrkbk.'sheet1$'n as xlssheet
set exist='e'
where upcase(strip(xlssheet.myfiles)) in (select upcase(strip(myfiles)) from FilesInDir)
;
select * from wrkbk.'sheet1$'n as xlssheet ;
quit;
LIBNAME WrkBk clear;