topic Re: ODS Excel | Proc tabulate in multiple sheet in SAS Programming

ODS Excel | Proc tabulate in multiple sheet

MariaD — Tue, 09 Apr 2019 15:15:55 GMT

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?

Re: ODS Excel | Proc tabulate in multiple sheet

Reeza — Tue, 09 Apr 2019 15:36:27 GMT

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&docsetVersion=9.4&locale=en

Re: ODS Excel | Proc tabulate in multiple sheet

MariaD — Tue, 09 Apr 2019 16:35:08 GMT

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,

Re: ODS Excel | Proc tabulate in multiple sheet

Reeza — Tue, 09 Apr 2019 16:35:56 GMT

I believe the setting you want is justification.

title j=l 'My title';

Re: ODS Excel | Proc tabulate in multiple sheet

MariaD — Tue, 09 Apr 2019 16:51:45 GMT

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

Re: ODS Excel | Proc tabulate in multiple sheet

Reeza — Tue, 09 Apr 2019 16:57:16 GMT

Is the J=L before the text or after?