topic Re: Read in multiple Excel files using Macro do-loop in SAS Programming

Read in multiple Excel files using Macro do-loop

runrunbunny — Wed, 13 Oct 2021 15:03:02 GMT

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

Re: Read in multiple Excel files using Macro do-loop

Reeza — Wed, 13 Oct 2021 15:09:21 GMT

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

Re: Read in multiple Excel files using Macro do-loop

runrunbunny — Wed, 13 Oct 2021 15:39:31 GMT

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

Re: Read in multiple Excel files using Macro do-loop

Reeza — Wed, 13 Oct 2021 15:40:59 GMT

Restart SAS and try it in a new session.
If not, then re-type each quote in the code. This can happen if you copied the cost from Word/PDF which add some invisible characters to the text sometimes.

Re: Read in multiple Excel files using Macro do-loop

runrunbunny — Wed, 13 Oct 2021 15:47:40 GMT

It works! I really appreciate your solution, Reeza. Have good day!