topic problem of using macro to import multiple csv files in SAS Procedures

problem of using macro to import multiple csv files

comeon2012 — Tue, 19 Jun 2012 05:19:50 GMT

Hi,

There are tens of csv files in folder D:\users\...\desktop\list. I need to import them into SAS.

For illustration purpose, I only take 2 files as the example.

%let country1=abudhabi;

%let country2=argentina;

%macro import;

%do i =1 % to 2;

proc import out = &&country&i

datafile="D:\users\...\desktop\list\&&country&i.csv"

dbms=csv replace;

run;

%end;;

%mend import;

%import;

But the codes did not work and the error messages are as below:

*WARNING: Apparent symbolic reference D not resolved;

*WARNING: Apparent symbolic reference COUNTRY1CSV not resolved;

*ERROR: Physical file does not exist,D:\Users\...\Desktop\list\&&country&i.csv;

*ERROR: Import unsuccessful. See SAS Log for details.;

Can anyone advise me what the problem is and how to resovle it? Thanks.

Re: problem of using macro to import multiple csv files

Alpay — Tue, 19 Jun 2012 05:44:25 GMT

&&country&i.csv will resolve to Country1csv for i=1. && will resolve to & and &i will resolve to 1 in the first pass.

Macro facility will try to resolve &Country1csv macro variable reference which fails.

The single period following macro variable reference will act as a delimiter and won't be part of the resulting text.

You will need to insert another '.' (period) after &&country&i to delimit the value of macro variable from the the file extension.

Replace '&&country&i.csv' to '&&country&i..csv'

I am not sure about the warning 'WARNING: Apparent symbolic reference D not resolved;' I could not see where macro variable D is referenced in the code.

Re: problem of using macro to import multiple csv files

comeon2012 — Tue, 19 Jun 2012 06:02:30 GMT

It still did not work when I add another period.

But after I accidentally added another two period, i.e, '&&country&i...csv', it worked although 'WARNING: Apparent symbolic reference D not resolved' still appeared. Anyways, the csv file were imported to SAS. Thank you Alpay.

Re: problem of using macro to import multiple csv files

Alpay — Tue, 19 Jun 2012 06:32:50 GMT

You are right. I did not test it before I submitted above code.

First period acts as a delimiter for &i in the first pass.

Second period acts as a delimiter for &Country1 in the second pass.

&&Country&i.csv -> Country1csv

&&Country&i..csv -> abudahbicsv

&&Country&i...csv -> abudhabi.csv