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

How to use Macro in referencing Excel worksheet names?

mnew — Wed, 20 Jun 2012 23:57:29 GMT

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)

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

Linlin — Thu, 21 Jun 2012 00:13:25 GMT

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

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

mnew — Thu, 21 Jun 2012 18:33:46 GMT

Magic! Thank you so much!