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
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;
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;
Do you have a CSV or Excel file?
Is each file different?
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.
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.
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";
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.