ODS Excel | Proc tabulate in multiple sheet

[MariaD]

Hi folks,

I need to generate an Excel file with more than one sheet. In each sheet, I have multiples reports (generated by proc tabulate), one below other. It's very important that the reports are one below other.

I tried the following code but it generates one sheet only with all reports (one below other) and not two sheets as required.

ODS EXCEL FILE="/Test.xlsx" 
    OPTIONS(START_AT="1,3" SHEET_INTERVAL="none" EMBED_TITLES_ONCE='yes' embedded_titles="yes" SHEET_NAME="Archivo 1") STYLE=Daisy;

/*Report 1*/
PROC TABULATE DATA=SASHELP.CLASS ;	
	VAR Age;
	CLASS Sex /	ORDER=UNFORMATTED MISSING;
	TABLE Sex,
		  N 
          Age*Sum ;
RUN;

/*Report 2*/
PROC TABULATE DATA=SASHELP.CLASS ;	
	VAR Age;
	CLASS Sex /	ORDER=UNFORMATTED MISSING;
	TABLE Sex,
		  N 
          Age*Sum ;
RUN;

ODS EXCEL OPTIONS (START_AT="1,3" SHEET_INTERVAL="none" EMBED_TITLES_ONCE='yes' embedded_titles="yes" SHEET_NAME="Archivo 2") STYLE=Daisy;
/*Report 3*/
PROC TABULATE DATA=SASHELP.CLASS ;	
	VAR Age;
	CLASS Sex /	ORDER=UNFORMATTED MISSING;
	TABLE Sex,
		  N 
          Age*Sum ;
RUN;

/*Report 4*/
PROC TABULATE DATA=SASHELP.CLASS ;	
	VAR Age;
	CLASS Sex /	ORDER=UNFORMATTED MISSING;
	TABLE Sex,
		  N 
          Age*Sum ;
RUN;


ODS _ALL_ CLOSE;

I tried removing the option  SHEET_INTERVAL="none" but it generate a sheet per proc tabulate, instead only two sheets. Any suggestion? 

[Reeza · Accepted solution]

Have you tried setting sheet_interval to NOW when you want the new page. Also, which version of SAS do you have? This was a known bug in a specific version, before TS1M4 I believe.


https://documentation.sas.com/?docsetId=odsug&docsetTarget=p09n5pw9ol0897n1qe04zeur27rv.htm&docsetVe...

[MariaD]

Thanks @Reeza ! One more question... I'm using "Daisy" style, but my only problem is I need the title align to the left and in this style align to center. I tried to add "align" option on title but it has not effect Any suggestion?

Regards, 

[Reeza]

I believe the setting you want is justification.

title j=l 'My title';

[MariaD]

@Reeza , yes, I tried it but it's completely ignored, the title remains on center.

[Reeza]

Is the J=L before the text or after?