importing multiple excel files to SAS datasets

[Raj_C]

Hi SAS Users,

I am converting excel to SAS dataset with the following code.

now I want to convert around 10 excel files to SAS datasets. instead of writing 10 time this same code can we have macro here.

I am using SAS EG 9.4

x 'dos2unix -437 /D/Raju/Compare/AE_details.csv /D/Raju/Compare/AE_details.csv';

PROC IMPORT
DATAFILE="/D/Raju/Compare/AE_details.csv"
OUT = ae REPLACE
DBMS=csv;
GETNAMES=YES;
RUN;

data ae_details;
set work.ae;
run;

KR,

Raju

[Kurt_Bremser · Accepted solution]

First, wrap your code into a macro definition:

%macro import_excel(infile);

x "dos2unix -437 /D/Raju/Compare/&infile..csv /D/Raju/Compare/&infile..csv";

PROC IMPORT
DATAFILE="/D/Raju/Compare/&infile..csv"
OUT = &infile REPLACE
DBMS=csv;
GETNAMES=YES;
RUN;

%mend;

Then, write your filenames into a control dataset:

data control;
length filename $30; *just to be sure;
input filename $;
cards;
AE_details
;
run;

Then, execute your macro from that dataset:

data _null_;
set control;
call execute('%import_excel('!!strip(filename)!!');');
run;

[Reeza]

Do you have a CSV or Excel file? 

Is each file different?

[Raj_C]

I am having Excel files. but when I tried with DBMS = xlsx it is giving following error. so I saved all files to CSV manually then imported.

ERROR: XLSX file does not exist.

all files are in same format.

[Kurt_Bremser]

As an addendum, I'd like to note that in a production environment I would not rely on proc import; instead every infile would get its hand-tailored data step. Which shall respond with a fail if the input structure (or values/types) changes unexpectedly.

[rogerjdeangelis]

If you happen to be using the old text editor you can run these dm commands. Also you can type one on the command line, hit the submit mouse button then '?' to get last submitted command, edit an submit.

You can also chain command line statements by separating them with a ';'

However as has been stated, your CSVs cannot have issues with headers or data.

I like to highlight one at a time and hit RMB.

dm "dimport 'c:\temp\tmp0.csv' class0  replace";
dm "dimport 'c:\temp\tmp1.csv' class1  replace";
dm "dimport 'c:\temp\tmp2.csv' class2  replace";
dm "dimport 'c:\temp\tmp3.csv' class3  replace";
dm "dimport 'c:\temp\tmp4.csv' class4  replace";
dm "dimport 'c:\temp\tmp5.csv' class5  replace";
dm "dimport 'c:\temp\tmp6.csv' class6  replace";
dm "dimport 'c:\temp\tmp7.csv' class7  replace";
dm "dimport 'c:\temp\tmp8.csv' class8  replace";
dm "dimport 'c:\temp\tmp9.csv' class9  replace";