How to export many formatted datasets to single Excel sheet?

[SAS93]

I have an interative macro code that runs crosstabs (with proc freq, of course) for each variable in a list that is stored in a macro, then saves each crosstab table as a separate dataset in a permanent location.

I would like to do something similar, but print out each of those datasets to a single Excel sheet so i can ultimately apply a custom table style and chart template to them.

My current iterative code is below:

%let varnames=
	list_of_variables; 


*2) Iterate through the macro list;
*2a) ODS selects only crosstab tables to print;
%macro dothis;
    %do i=1 %to %sysfunc(countw(&varnames));
         %let thisname=%scan(&varnames,&i,%str( ));
		*PROC FREQ, output ODS tables;
			ODS Select CrossTabs;
			ODS output CrossTabs=year_&thisname;
				Proc freq data=rollrpt3 
						Tables year*(&thisname) / row cl nostd nototal;
				Run;
		*OUTPUT SIMPLE TABLES TO PERMANENT LOCATION;
			Data AUTOLIB.year2_&thisname;
				Set year_&thisname;
				DROP Table F_: WgtFreq Percent LowerCL UpperCL _SkipLine;
			Run; 

			ods excel file='C:\Summary Reports_2018-forward\Rolling Reports\AUTOLIB\Exports.xls' style=JOURNAL;
				PROC PRINT NOOBS DATA=AUTOLIB.YEAR2_&THISNAME;
				RUN;
			ODS Tagsets.ExcelXP CLOSE;
		/*OUTPUT PERMANENT SIMPLE TABLES TO EXCEL wORKBOOK;
				proc export data=autolib.year2_&thisname
				    outfile="C:\Summary Reports_2018-forward\Rolling Reports\AUTOLIB\Exports.xlsx"
				    dbms=xlsx
				    replace label;
				    sheet="&thisname";
				run;	*/
	    %end;
	%mend;
%dothis

[Tom]

If you want ODS EXCEL to write new procedure output to the same sheet then just change the SHEET_INTERVAL option.

But why not just skip the macro code and instead transpose the data so you can just run all of the variables at once using a BY statement?

[SASKiwi]

One of the limitations of ODS is that you can't append to an existing output - you have to create the complete output file in one continuous process. That means if you want to continue using ODS Excel then you need to move the first ODS Excel statement before the start of your DO loop. Using a sheet interval of NONE you should then be able to run multiple PROC PRINTs to the same sheet.  

[SAS93]

Thank you! I've tried that and it works *sometimes*...? I got it to work when I initially ran the revised code block, but now after I've saved, closed, and returned to the SAS file, it's still just outputting the first variable in the macro list. Any idea why? 

%macro dothis;
	ods excel file='C:\Annual Summary Reports_2018-forward\Rolling Reports\AUTOLIB\Exports.xlsx'; 
	ods excel options(sheet_interval="none");
	    %do i=1 %to %sysfunc(countw(&varnames));
	         %let thisname=%scan(&varnames,&i,%str( ));
			*PROC FREQ, output ODS tables;
				ODS Select CrossTabs;
				ODS output CrossTabs=year_&thisname;
					Proc surveyfreq data=rollrpt3 total=TOTALS_ NOMCAR VARHEADER=LABEL;
						Weight WTANAL;
						Strata sud_nest;
							Tables year*(&thisname) /noprint row cl nostd nototal;
					Run;
			*OUTPUT SIMPLE TABLES;
				Data year2_&thisname;
					Set year_&thisname;
					DROP Table F_: WgtFreq Percent LowerCL UpperCL _SkipLine;
					Rename Frequency=n	RowPercent=Row_Percent	RowLowerCL=Lower_CL	RowUpperCL=Upper_CL;
					Format Frequency comma7. Rowpercent RowLowerCL RowUpperCL 7.2;
				Run; 
			*Print simple tables and output to Excel sheet;
				PROC PRINT NOOBS DATA=YEAR2_&THISNAME;
				RUN;
		ODS excel CLOSE;
		    %end;
	%mend;
%dothis

[SAS93]

I figured out that changing the ODS Excel CLOSE; at the bottom of the macro for ODS Tagsets.ExcelXP CLOSE; allows all tables to be output to the same Excel sheet, while still of course maintaining the formatting I specified.

I'm not sure why this was the hiccup...but I resolved it.

[Tom]

---

@SAS93 wrote:
I figured out that changing the ODS Excel CLOSE; at the bottom of the macro for ODS Tagsets.ExcelXP CLOSE; allows all tables to be output to the same Excel sheet, while still of course maintaining the formatting I specified.

I'm not sure why this was the hiccup...but I resolved it.

---

So you fixed it by closing something you didn't open?

The issue is clear just looking at the structure of your macro.  Fixing the indentation will make it more obvious.

%macro dothis;
ods excel file='C:\Annual Summary Reports_2018-forward\Rolling Reports\AUTOLIB\Exports.xlsx'; 
ods excel options(sheet_interval="none");
  %do i=1 %to %sysfunc(countw(&varnames));
...
ODS excel CLOSE;
  %end;
%mend;

Move the ODS EXCEL CLOSE to AFTER the %END of the %DO loop.