DATA Step, Macro, Functions and more

importing multiple excel files to SAS datasets

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

importing multiple excel files to SAS datasets

 

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


Accepted Solutions
Solution
‎09-19-2016 09:55 AM
Super User
Posts: 6,928

Re: importing multiple excel files to SAS datasets

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎09-19-2016 09:55 AM
Super User
Posts: 6,928

Re: importing multiple excel files to SAS datasets

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 17,748

Re: importing multiple excel files to SAS datasets

[ Edited ]

Do you have a CSV or Excel file? 

Is each file different?

Contributor
Posts: 28

Re: importing multiple excel files to SAS datasets

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.

Super User
Posts: 6,928

Re: importing multiple excel files to SAS datasets

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Valued Guide
Posts: 505

Re: importing multiple excel files to SAS datasets

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";
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 377 views
  • 1 like
  • 4 in conversation