sas looking for a physical file

[deleted_user]

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?

[sbb]

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.

[Patrick]

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;