BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Raj_C
Obsidian | Level 7

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

5 REPLIES 5
Kurt_Bremser
Super User

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
Super User

Do you have a CSV or Excel file? 

Is each file different?

Raj_C
Obsidian | Level 7

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
Super User

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
Barite | Level 11
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";

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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