I'm new to SAS macros, but have put some together using online help and other resources to (mostly) automate a SAS program I need to regularly run. My current code starts off by setting up a dataset using survey data (establishing weight and strata vars, recoding, etc.). Then I have several separate macro programs that perform separate tasks meant to output various pieces I use later for putting into the report's Word document. These macros are solely for iterative/space-saving purposes to run a certain procedure once for each of the many variables I tell it to use.
The data step and the subsequent proc steps (within their macros) all run perfectly when I run them one at a time. The issue I'm having is that running the entire code itself either results in skipped output from the macros while other output is produced, the "most recent" macro to run will output its data under the name of the file that was supposed to be produced before it, or this same thing will happen with two+ output files--the output will be perfect, but the file names will be reversed.
I understand that macros are compiled first in SAS code, so I'm not sure if its an issue with invocation or something entirely different, but I'd appreciate anything I've overlooked. An example of my ugly macros are below; I'm not able to share actual data due to its sensitivity:
* Set macro list of vars;
%let varnames=
RACE2 age BMI FPL WIC Division Urban Married edu
insur
intended
any_method
effective
pre_vist PNC_1TRM checkup flu FLUPREG DDS_Cln
pre_diet pre_exer mvitamin vitamin
sleep sleep_alone approved_surface
BF5EVER wk_length;
ods html close;
ODS NOPROCTITLE;
%macro yearly;
ods excel file="C:\Exports_&startyr-&endyr..xlsx";
ods excel options(sheet_interval="none" sheet_name="Original_5 Year");
%do i=1 %to %sysfunc(countw(&varnames));
%let thisname=%scan(&varnames,&i,%str( ));
*PROC FREQ through list;
ODS exclude Summary;
ODS exclude VarianceEstimation;
ODS select CrossTabs;
ODS output CrossTabs=year_&thisname;
Proc surveyfreq data=rollrpt3 total=TOTALS NOMCAR;
Weight weightvar;
Strata stratvar;
Tables year*(&thisname) / row cl nofreq nostd nototal;
Run;
ODS Tagsets.ExcelXP CLOSE;
%end;
%mend;
%yearly
/***************************************** Only Data Exports for Excel Charts *****************************************/
%let charnames=
Insur
pre_vist DDS_Cln
MOM_BMIG_QX_REV
vitamin
smk63B_A smk63L_A smk6NW_A
violence
sleep sleep_alone approved_surface
wk_length;
** Formatted Reports to output;
%macro charts;
*EXCEL output, once everything is printed;
ods excel file="C:\For_Charts.xlsx";
ods excel options(sheet_interval="none" sheet_name="For Charts");
%do i=1 %to %sysfunc(countw(&charnames));
%let thisname=%scan(&charnames,&i,%str( ));
TITLE;
Proc report data=year_&thisname
nowd CENTER WRAP;
Column &thisname Year,(RowPercent) dummyvar;
Define &thisname / order=internal group flow;
Define Year / across;
Define RowPercent / "%" display;
/* Dummy var for alignment */
Define dummyvar / computed noprint ;
compute dummyvar;
dummyvar = 1;
endcomp;
Run;
ODS Tagsets.ExcelXP CLOSE;
%end;
%mend;
%charts
Two things that may relate to your specific problem is that you use
ods excel file="C:\Exports_&startyr-&endyr..xlsx";
And attempt to close with
ODS Tagsets.ExcelXP CLOSE;
So you are not actually closing the EXCEL when you think you are.
Match the ODS destination name with the correct CLOSE and that may help.
Personally I haven't ever understood the desire to "export" data to Excel for charting. I learned SAS graphing a long time before dealing with spreadsheets and constantly fought with the stupid things needed to make lots of similar graphs when code was more flexible.
Now a general coding style approach. I would suggest separating the creation of the data sets from creating external files at all. That way you can generate one data set with all the output and select from that file as needed. If datasets get moderately large then loading the data set into an analysis proc like Surveyfreq 28 times (once for each variable in Varnames) can become noticeable. You can get the summary for all the variables with
Tables year * (&varnames)
Then you get all of your results in one table that can be processed without looping and quite possibly use BY processing to place separate tables in different sheets of a single excel, or maybe even make the graphs.
Turn on the MPRINT option so you can see what SAS code the macros are generating.
To make your code more manageable move all of the macro definitions (the lines from %MACRO to %MEND) to the TOP of the program. Do not nest macro definitions inside of other macro definitions.
Then start adding your %LET and macro calls to build the code you want to actually run.
Your macros do not appear to be defining the macro variables they use.
For example your first one is using the macro variable I to control a %DO loop. You should define I as %LOCAL so when you run the macro it will not modify the value of any already existing macro variable named I.
Also they do not appear to have any INPUTs. For example that first one %YEARLY. Right now the macro is using "magic" macro variables. In the middle of the macro it suddenly references the macro variable VARNAMES whose value must have been assigned by magic since it is not declared as either a parameter to the macro nor as a local macro variable nor is it defined as %GLOBAL or even tested for existence by calling the %SYMEXIST() function before its value is used. Add VARNAMES as a parameter to the macro and supply the list when you call the macro.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.