Hi Everyone,
I have 300 Excels files (file1, file2, file3.....file300) need to be read into SAS. Here is a hard code I tested and it runs well:
PROC IMPORT Out=output1
Datafile= "data\file1.xlsx"
DBMS=XLSX REPLACE;
SHEET='Sheet1';
GETNAMES=YES;
Run;
Since it's not efficient to call the Macro variable 300 times by using:
%Readin(file1);
%Readin(file2);
%Readin(file3);
...
...
%Readin(file300);
So I use a do-loop in this case, but the code doesn't work. Do you have any suggestions that where I did incorrectly?
%macro Readin;
%do i = 1 %to 300;
PROC IMPORT Out=output&i
Datafile= "data\file&i..xlsx"
DBMS=XLSX REPLACE;
SHEET='Sheet1';
GETNAMES=YES;
Run;
%end
%mend;
%Readin;
Thank you,
R.B
options mprint symbolgen;
%macro Readin;
%do i = 1 %to 3;
PROC IMPORT Out=output&i
Datafile= "data\file&i..xlsx"
DBMS=XLSX REPLACE;
SHEET='Sheet1';
GETNAMES=YES;
Run;
%end
%mend;
%Readin;
What does "not work" mean? Can you post the log from the code above?
@runrunbunny wrote:
Hi Everyone,
I have 300 Excels files (file1, file2, file3.....file300) need to be read into SAS. Here is a hard code I tested and it runs well:
PROC IMPORT Out=output1
Datafile= "data\file1.xlsx"
DBMS=XLSX REPLACE;
SHEET='Sheet1';
GETNAMES=YES;
Run;
Since it's not efficient to call the Macro variable 300 times by using:
%Readin(file1);
%Readin(file2);
%Readin(file3);
...
...
%Readin(file300);
So I use a do-loop in this case, but the code doesn't work. Do you have any suggestions that where I did incorrectly?
%macro Readin;
%do i = 1 %to 300;
PROC IMPORT Out=output&i
Datafile= "data\file&i..xlsx"
DBMS=XLSX REPLACE;
SHEET='Sheet1';
GETNAMES=YES;
Run;
%end
%mend;
%Readin;
Thank you,
R.B
Hi Reeza,
Thank you so much for your reply. Below is the log says I have unbalanced quotation marks:
NOTE: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation
marks.
But I don't see any unmatched quotation marks in the code. Could you please kindly advise? @Reeza
Thank you,
R.B
It works! I really appreciate your solution, Reeza. Have good day!
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.