How to use Macro in referencing Excel worksheet names?

[mnew]

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)

[Linlin]

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

[mnew]

Magic! Thank you so much!