Right, to answer your first question is two part. Firstly you need to read in a directory listing from your OS. This can be done in various ways, here is a Windows version using piping. The second part is then to macro your code to be called for each file:
/* Macro imports file */
%macro Read (fname=);
proc import datafile="c:\temp\&fname."
out=work.test
replace
dbms=xls;
options validvarname=v7;
getnames=yes;
run;
%mend Read;
/* Get list of files in given directory */
filename tmp pipe 'dir "c:\temp\*.xls" /b';
data _null_;
length fn $200;
infile tmp;
input fn $;
/* This generates one call to the macro per file from the read in */
call execute(cats('%Read (fname=',fn,');'));
run;
You will also notice consistent casing, indentation and such like, and using the code insertion ( {i} above where you post) to retain all this, makes code easier to read. Now the above is a bit more advanced, so if any of that doesn't make sense, its probably a good idea to read up on all that in the manual an understand what you are doing.
And, here is the big one. You say you are reading output Excel files back into SAS for some sort of comparison, correct? If so then I will say this. Anything you read from Excel via proc import will be guarenteed not to match your original data. Proc import is a guessing procedure, it checks some rows of data and guesses what the data is. This is one primary reason why I would never use proc import to read in data - I always specify incoming data. Second to this is Excel itself having no structure or consistency - it is not a database or data transfer format. Imagine if you will that you play the lottery, you choose the "pick numbers at random" and combine that with the extreme unlikliness of winning anyways, do you think you will win - well there is a tiny fraction of a chance.
I would say, assess what your process is, why are you doing this? What I tend to do for my outputs is to store a "one step away" dataset, this is a dataset which matches exactly to the output requirements. All columns an sorting is present, all columns have fixed naming and properties. A QC can then separately create the same dataset and QC/compare that. The output is generated by a bare bones proc report, with a manual look at the output file - which you would have to do in any case - after that.
... View more
