Help using Base SAS procedures

sas looking for a physical file

Reply
N/A
Posts: 0

sas looking for a physical file

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?
Super Contributor
Super Contributor
Posts: 3,174

Re: sas looking for a physical file

Posted in reply to deleted_user
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.
Respected Advisor
Posts: 4,173

Re: sas looking for a physical file

Posted in reply to deleted_user
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;
Ask a Question
Discussion stats
  • 2 replies
  • 106 views
  • 0 likes
  • 3 in conversation