Trouble running entire program with multiple macro proc steps

[SAS93]

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

[ballardw]

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.

[Tom]

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.