Using Prompt Values in ODS Tagsets Excel Sheet Names

[NathanOch]

Is there a way to use prompt values from the stored process in the sheet name of your ODS Tagsets.excelxp output?

Thanks,

Nate

[Timmy2383]

Sure. Just create a text box prompt or something then include SHEET_NAME="&SHEET" in the ODS TAGSETS.EXCELXP options (assuming you name your prompt SHEET) .

You might also need SHEET_INTERVAL="none", but can't remember off the top of my head.

[NathanOch]

That's what I thought, but it seems my BODY statement overrides the SHEET_NAME statement and without the BODY statement nothing is generated.

[Timmy2383]

Can you provide the code?

[NathanOch]

Here is my output statement:

*ProcessBody;

%global _odsest _odsstyle _odsstylesheet;

%let _odsest=html;

*%let _odsstyle=analysis;

%let _odsstylesheet=;

data _null_;

/* Set HTTP headers */

rc = stpsrv_header('Content-type','application/vnd.ms-excel');

/* Prompt to SAVE or OPEN the attachment file named test.xls using Excel */

rc = stpsrv_header('Content-disposition','attachment; filename=Open_Inventory_List.xls');

/* OR */

/* Open results directly to Excel (browser-dependent) */

/* rc = stpsrv_header('Content-disposition',"inline"); */

run;

ODS LISTING CLOSE;

*%let _ODSSTYLE=analysis;

ODS TAGSETS.ExcelXP options(sheet_name="&new_Client-&new_Sub_Client" sheet_interval="none" embedded_titles='yes') BODY="Open_Inventory_List" /*style=analysis*/;

%stpbegin;

...my proc print statement...

%stpend;

[Vince_SAS]

Try this simplified code:

*ProcessBody;

%let RV=%sysfunc(appsrv_header(Content-type,application/vnd.ms-excel));
%let RV=%sysfunc(appsrv_header(Content-disposition,attachment; filename="Open_Inventory_List.xml"));

ods _all_ close;

ods tagsets.ExcelXP file=_webout style=analysis;

ods tagsets.ExcelXP options(sheet_name="&NEW_CLIENT-&NEW_SUB_CLIENT"
                            sheet_interval='none'
                            embedded_titles='yes');

* Your proc print statement here;

ods tagsets.ExcelXP close;

Adapted from page 18 of this paper:
http://support.sas.com/resources/papers/proceedings15/SAS1700-2015.pdf

Vince DelGobbo
SAS R&D

[NathanOch · Accepted solution]

Turns out if you put the ODS Tagsets statement AFTER the %stpbegin statement and exclude the BODY statement you can successfully use SHEET_NAME.