Help using Base SAS procedures

sas looking for a physical file

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

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 website using either the site SEARCH facility or the Google advanced argument below:

update excel worksheet

Scott Barry
SBBWorks, Inc.
Respected Advisor
Posts: 3,887

Re: sas looking for a physical file

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).

/* read files in directory */
filename myfiles pipe 'dir "c:\SASUSER\*.sas" /B/ON';
data FilesInDir;
infile myfiles truncover;
input myfiles $50.;
put _infile_;
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 ;
LIBNAME WrkBk clear;
Ask a Question
Discussion stats
  • 2 replies
  • 3 in conversation