DATA Step, Macro, Functions and more

How to use Macro in referencing Excel worksheet names?

Reply
Frequent Contributor
Posts: 100

How to use Macro in referencing Excel worksheet names?

Hi Experts:

I’m trying to learn macro. I have an Excel2010 file with multiple worksheets and their names all end with “_rpt”. For example the first tab’s name is Sales_rpt and the second is Budget_rpt.  I want to write a macro that makes it easier for me to create SAS dataset from specified sheets.

Here is my failed attempt to create a SAS dataset called Sales by using a data step reading in the Sales_rpt worksheet.

What did I do wrong?

Thank you!

Libname rpt ‘….xlsx';

%macro getdata(DSN);

Data work.&DSN;

      set rpt."&DSN_rpt$"n;

run;

%mend;

%getdata(Sales)

Super Contributor
Posts: 1,636

Re: How to use Macro in referencing Excel worksheet names?

Hi,

try

Libname rpt ‘….xlsx';

%macro getdata(DSN);

Data work.&DSN;

      set rpt."&DSN._rpt$"n;

run;

%mend;

%getdata(Sales)

/* you need a . after  macro variable &dsn. */


Linlin

Frequent Contributor
Posts: 100

Re: How to use Macro in referencing Excel worksheet names?

Magic! Thank you so much!

Ask a Question
Discussion stats
  • 2 replies
  • 200 views
  • 1 like
  • 2 in conversation