Proc-importing multiple excel files using %macro

[KS99]

Hi, all, 

Greetings, 

I hope you are enjoying a snug weekend evening; 

Can you guys simply check why this code is not working? 

%MACRO import(index);
proc import datafile='C:/Users/somebody/Desktop/Country data/&index.xls'
dbms=xls out=&index replace; run; %MEND import;

Then, I tried to execute macro %import as follows: 

%import(file1); %import(file2); %import(brabra.... etc. 

However, it didn't work, and what I got was only

ERROR: Physical file does not exist, C:/Users/somebody/Desktop/Country data/&index.xls

What seems to be a problem in this coding? 

Thank you in advance! 

Sincerely, 

KS -, 

[ballardw · Accepted solution]

You have two problems. The major one regards quotes. Macro variables do no resolve inside single quotes.

Second, the macro language uses the . (dot) to indicate the end of a macro variable in conjunction with other text. So when you have macro variable such as &index.xls the . would be removed and the resolved text would be come File1xls with no dot before the file extension. So when using that construct you need two dots.

So you code likely should look like:

%MACRO import(index);
   proc import datafile="C:/Users/somebody/Desktop/Country data/&index..xls"
     dbms=xls out=&index replace; 
   run; 
%MEND import;

Please post code in a text or code box opened on the forum with the </> or running man icon. The message windows will reformat text making it harder to read and follow.

You can see the code generated by your macro by using OPTIONS MPRINT; before executing the macro.

If these XLS (really!?) files are supposed to be of the same structure for variable names, types and lengths do not be surprised that the data sets generated by Proc Import do not agree as each file is processed separately.

[KS99]

Thank you so much, ballardw!
I wish you a very good week!

KS -

[mkeintz]

The log error message shows that the macrovar &index is not being seen by the macro processor, so it is not resolved  (e.g. you want &index to be resolved to file1).

That's because your macro code has single quotes in this line:

proc import datafile='C:/Users/somebody/Desktop/Country data/&index.xls'

which should probably have double quotes as in

proc import datafile="C:/Users/somebody/Desktop/Country data/&index..xls"

The double quotes will allow the macro parser to look inside the quoted text and resolve &index.

Also note that the single . after &index, it now has a double ..   - which allows the first . to serve as a macrovar terminator, therefore requiring a second . to be part of the final file name ending with ".xls".

[KS99]

I really appreciate your comments, mkeintz!
Wish you a very good evening!

Sincerely,
KS -,

[SASKiwi]

In addition to the other issues raised:

- Windows paths typically use back slashes not forward slashes, although most likely they will still work.

- If your SAS session is running on a remote SAS server you can't write to your own desktop drives. You will have to use a folder the remote server recognises.

[KS99]

Thank you SASkiwi!

I will keep them in mind!

Sincerely,
KS -,