topic Re: Macro naming via data set/report in SAS Programming

Macro naming via data set/report

Dsrountree — Wed, 17 Sep 2014 12:40:00 GMT

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;

Re: Macro naming via data set/report

data_null__ — Wed, 17 Sep 2014 12:46:00 GMT

Use double quotes sheet_name="tb4"

Re: Macro naming via data set/report

RW9 — Wed, 17 Sep 2014 12:47:25 GMT

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.

Re: Macro naming via data set/report

Dsrountree — Wed, 17 Sep 2014 12:55:01 GMT

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?

Re: Macro naming via data set/report

Dsrountree — Wed, 17 Sep 2014 13:04:48 GMT

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.

Re: Macro naming via data set/report

RW9 — Wed, 17 Sep 2014 13:31:09 GMT

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.