topic Re: Using Prompt Values in ODS Tagsets Excel Sheet Names in Developers

Using Prompt Values in ODS Tagsets Excel Sheet Names

NathanOch — Thu, 28 Apr 2016 17:05:13 GMT

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

Thanks,

Nate

Re: Using Prompt Values in ODS Tagsets Excel Sheet Names

Timmy2383 — Thu, 28 Apr 2016 17:10:02 GMT

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.

Re: Using Prompt Values in ODS Tagsets Excel Sheet Names

NathanOch — Thu, 28 Apr 2016 17:20:06 GMT

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

Re: Using Prompt Values in ODS Tagsets Excel Sheet Names

Timmy2383 — Thu, 28 Apr 2016 17:21:18 GMT

Can you provide the code?

Re: Using Prompt Values in ODS Tagsets Excel Sheet Names

NathanOch — Thu, 28 Apr 2016 17:24:01 GMT

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;

Re: Using Prompt Values in ODS Tagsets Excel Sheet Names

NathanOch — Thu, 28 Apr 2016 18:02:08 GMT

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

Re: Using Prompt Values in ODS Tagsets Excel Sheet Names

Vince_SAS — Fri, 29 Apr 2016 12:09:14 GMT

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