Using Summary Tables to create an Excel file with tabs named after variable in Pages Task Role

[runningjay]

I'm a SAS EG user and have limited experience with direct SAS programming. I'm currently using SAS EG version 8.2 Update 1 (8.2.1.1223). I would like to accomplish this project in SAS EG if possible.

One of the output options I've selected for a Summary Table I've created is Excel. I would like to have each tab in the Excel output named based on the field I've added to the Pages task role so that the variable appears as the tab name in excel. When I run the summary table and open the excel file the tabs are named Tabulate 1 - Table 1, Tabulate 2 - Table 1, etc, but instead I want to name the tab based on the variable in Pages.

Is there a way to do this within the Summary Tables wizard or by adjusting the code generated by the wizard so that the Excel output has a tab for the variable in the Pages Task Role?

Any guidance you can offer is appreciated!

[jennifert]

Hi @runningjay ,

I think you can do this by modifying the code that SAS automatically generates for you and using the ODS EXCEL statement.  Here is an earlier post about this same question:

ODS Excelxp PROC TABULATE multiple sheets 

And note that in order to see the ODS EXCEL statement in the automatically generated code, you might need to enable the Display all generated SAS code in task output option under Tools > Options > Tasks > General.

I hope this helps!

thanks,

Jennifer

[runningjay]

Thanks for the assistance. I enabled the option to display all SAS code, but I'm not sure where I make the adjustment to have each excel output tab named after the value in the Pages task role. Here is a simplified example using data from the CARS table. I'm trying to make each tab name equal the value in the MAKE field. Where would I adjust this code?

ODS _ALL_ CLOSE;

ODS PROCTITLE;

OPTIONS DEV=PNG;

GOPTIONS XPIXELS=0 YPIXELS=0;

%macro HTML5AccessibleGraphSupported;

    %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;

%mend;

FILENAME EGSR TEMP;

ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR

    STYLE=HTMLBlue

    NOGTITLE

    NOGFOOTNOTE

    GPATH=&sasworklocation

    ENCODING=UTF8

    options(rolap="on")

;

FILENAME EGXLSSX TEMP;

ODS EXCEL(ID=EGXLSSX) FILE=EGXLSSX STYLE=HTMLBlue

OPTIONS (

 EMBEDDED_TITLES="yes" EMBEDDED_FOOTNOTES="yes"

);

TITLE;

FOOTNOTE;

/* -------------------------------------------------------------------

   Code generated by SAS Task

   Generated on: Wednesday, June 17, 2020 at 8:39:19 AM

   By task: Summary Tables

   Input Data: SASApp:SASHELP.CARS

   Server:  SASApp

   ------------------------------------------------------------------- */

/* -------------------------------------------------------------------

   Run the tabulate procedure

   ------------------------------------------------------------------- */

PROC TABULATE

DATA=SASHELP.CARS

     ;

     VAR MSRP Invoice;

     CLASS DriveTrain /    ORDER=UNFORMATTED MISSING;

     CLASS Origin /  ORDER=UNFORMATTED MISSING;

     CLASS Type /    ORDER=UNFORMATTED MISSING;

     CLASS Model /   ORDER=UNFORMATTED MISSING;

     CLASS Make /    ORDER=UNFORMATTED MISSING;

     TABLE /* Page Dimension */

Make,

/* Row Dimension */

Model*

  Type*

    Origin,

/* Column Dimension */

Mean*

  Invoice       ;

     ;

RUN;

/* -------------------------------------------------------------------

   End of task code

   ------------------------------------------------------------------- */

RUN; QUIT;

TITLE; FOOTNOTE;

%LET _CLIENTTASKLABEL=;

%LET _CLIENTPROCESSFLOWNAME=;

%LET _CLIENTPROJECTPATH=;

%LET _CLIENTPROJECTPATHHOST=;

%LET _CLIENTPROJECTNAME=;

;*';*";*/;quit;run;

ODS _ALL_ CLOSE;

[Cynthia_sas · Accepted solution]

Hi:

  In this case, I think it is easiest if you make the PAGE variable the same as a BY variable and then you can use ODS EXCEL suboptions to cause the sheets to be named automatically, as shown below:

I've highlighted the statements I added. Note that you may not need the first PROC SORT step, but I put it there so I could make a smaller subset as proof of concept without using ALL of SASHELP.CARS. But you can't use a BY statement in PROC TABULATE unless the data are sorted. Also, I believe it might be easier to export the code from the TABULATE task and then modify it. There aren't a lot of statements/options to add, but I would edit the code instead of doing it in a task.

Hope this helps,

Cynthia

[runningjay]

Very helpful - thank you!