Macro naming via data set/report

[Dsrountree]

Prior to introducing the macro to this part of the code – each section would create an XML file based on the sheet_name=’     ‘;

Now that I’ve created a macro that reads each data set and performs the function for all eight datasets the naming isn’t working.

Perhaps it’s something simple missing between the ‘   ‘ – ex: ‘&&tb4’ right now it writes each tab for the output data as tb4, tb4 2, tb4 3, …… incrementing by 1 after the tb4.

I’m not sure – New Jack SAS Guy

ODS TAGSETS.EXCELXP file='/windows/Infodata/DRountree/CAB/2014_2015_Competitive_Analysis_HMO_NYC.xml';

*** Define macro3 ***;

%macro macro3(tb4);

ODS TAGSETS.EXCELXP options(sheet_interval='table' suppress_bylines='yes' sheet_name= '&tb4');

PROC REPORT DATA = &tb4  spanrows nowd split='~';

columns CategoryCode CategoryDescription ID_:;

Define CategoryCode / order order=internal noprint;

define CategoryDescription / order 'Plan Name~Current Enrollment~Star Rating~Contract Number/PBP';

define ID_: / display;

compute after CategoryCode;

line ' ';

endcomp;

run;

%mend macro3;

*** Run macro3 ***;

%macro3(Aetna_14);

%macro3(Empire_BCBS_14);

%macro3(Healthfirst_14);

%macro3(UH_AARP_14);

%macro3(Aetna_15);

%macro3(Empire_BCBS_15);

%macro3(Healthfirst_15);

%macro3(UH_AARP_15);

ODS _ALL_ CLOSE;

[data_null__ · Accepted solution]

Use double quotes sheet_name="tb4"

[RW9]

Two things, first you need to enclose macro variables in double quotes:

sheet_name= "&tb4");

Otherwise they will not resolve.

The second point is when testing macros ensure you use options mlogic mprint symbolgen; so that you can see what everything is doing in your log, it makes it a lot easier.

[Dsrountree]

Thank you so much for the prompt reply....

Keep in mind I'm new to SAS - 1.7 years total.

What is options mlogic mprint symbolgen?

[Dsrountree]

Reading about it via Google - SAS.com - a little above my head but I understand why it's useful.

Since I'm new to this I write the SQL first and test the output to ensure data integrity then if needed create the macro.

[RW9]

Yes, the

options ....;

Sets system options.  Some of these could change paper layout, papersize, where to output to etc.  The ones above are specifically for macro debugging.  They create a lot more detail in the log, for example showing what the macro variable actually is at the point the code is run.