topic importing multiple excel files to SAS datasets in SAS Programming

importing multiple excel files to SAS datasets

Raj_C — Mon, 19 Sep 2016 12:10:37 GMT

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

Re: importing multiple excel files to SAS datasets

Kurt_Bremser — Mon, 19 Sep 2016 12:55:00 GMT

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;

Re: importing multiple excel files to SAS datasets

Reeza — Mon, 19 Sep 2016 13:27:51 GMT

Do you have a CSV or Excel file?

Is each file different?

Re: importing multiple excel files to SAS datasets

Raj_C — Mon, 19 Sep 2016 14:06:59 GMT

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.

Re: importing multiple excel files to SAS datasets

Kurt_Bremser — Mon, 19 Sep 2016 14:12:16 GMT

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.

Re: importing multiple excel files to SAS datasets

rogerjdeangelis — Mon, 19 Sep 2016 14:52:54 GMT

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