topic Re: Proc-importing multiple excel files using %macro in SAS Programming

Proc-importing multiple excel files using %macro

KS99 — Mon, 18 Apr 2022 01:31:37 GMT

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 -,

Re: Proc-importing multiple excel files using %macro

ballardw — Mon, 18 Apr 2022 14:54:41 GMT

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.

Re: Proc-importing multiple excel files using %macro

mkeintz — Mon, 18 Apr 2022 02:49:49 GMT

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

Re: Proc-importing multiple excel files using %macro

SASKiwi — Mon, 18 Apr 2022 03:15:47 GMT

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.

Re: Proc-importing multiple excel files using %macro

KS99 — Tue, 19 Apr 2022 04:23:37 GMT

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

KS -

Re: Proc-importing multiple excel files using %macro

KS99 — Tue, 19 Apr 2022 04:24:30 GMT

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

Sincerely,
KS -,

Re: Proc-importing multiple excel files using %macro

KS99 — Tue, 19 Apr 2022 04:25:05 GMT

Thank you SASkiwi!

I will keep them in mind!

Sincerely,
KS -,