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;
Use double quotes sheet_name="tb4"
Use double quotes sheet_name="tb4"
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.
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?
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.