Your SAS programs, embedded in web apps and elsewhere

Using Prompt Values in ODS Tagsets Excel Sheet Names

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

Using Prompt Values in ODS Tagsets Excel Sheet Names

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

 

Thanks,

Nate


Accepted Solutions
Solution
‎04-28-2016 02:02 PM
Contributor
Posts: 33

Re: Using Prompt Values in ODS Tagsets Excel Sheet Names

Posted in reply to NathanOch

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

View solution in original post


All Replies
Regular Contributor
Posts: 172

Re: Using Prompt Values in ODS Tagsets Excel Sheet Names

Posted in reply to NathanOch
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.
Contributor
Posts: 33

Re: Using Prompt Values in ODS Tagsets Excel Sheet Names

Posted in reply to Timmy2383

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

Regular Contributor
Posts: 172

Re: Using Prompt Values in ODS Tagsets Excel Sheet Names

Posted in reply to NathanOch
Can you provide the code?
Contributor
Posts: 33

Re: Using Prompt Values in ODS Tagsets Excel Sheet Names

Posted in reply to Timmy2383

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;

SAS Super FREQ
Posts: 304

Re: Using Prompt Values in ODS Tagsets Excel Sheet Names

Posted in reply to NathanOch

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

Solution
‎04-28-2016 02:02 PM
Contributor
Posts: 33

Re: Using Prompt Values in ODS Tagsets Excel Sheet Names

Posted in reply to NathanOch

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 505 views
  • 1 like
  • 3 in conversation