BookmarkSubscribeRSS Feed
sunilreddy
Fluorite | Level 6

Hi,

In sas 9.1,

using tagsets.ExcelXP to generate multiple work sheets in excel
But i am getting both reports are appending in single work sheet instead of two work sheets in excel. Could u pls let me know if i need to use any option to get multiple sheets


*ProcessBody;
ods listing close;
data _null_;
rc= stpsrv_header('Content-type', 'application/vnd.ms-excel');
rc= stpsrv_header('Content-disposition', 'attachment;
filename= muliple sheets.xls');
run;

%stpbegin;


ods tagsets.ExcelXP options(sheet_name="REP1");

proc report data=report1;
run;

ods tagsets.ExcelXP options(sheet_name="REP2");

proc report data=report2;
run;


ods tagsets.ExcelXP close;

%stpend;

1 REPLY 1
Cynthia_sas
SAS Super FREQ

Hi:

I see a few possible things to correct. Generally, when you use %STPBEGIN, you do NOT use the STPSRV_HEADER function. Or, if you are using the STPSRV_HEADER function, then you use FILE=_WEBOUT and do NOT use %STPBEGIN/%STPEND. There have been many previous postings about creating TAGSETS.EXCELXP output. And quite a few of the postings have included complete and working code. In addition there are some Tech Support notes about using STPSRV_HEADER:

http://support.sas.com/kb/12/994.html (although examples 4 and 5 show using %stpbegin/%stpend, do note that those are PDF and RTF examples and are not immediately relevant to what you are trying to do).

At the very least, I would recommend that you try this:

1) REMOVE the %STPBEGIN/%STPEND from your stored process code since you have the ODS statements. By default, without an override to &_ODSDEST, the output created is probably either SASReport XML or HTML. Since you are using the STPSRV_HEAER function, then you must be submitting this SP through the Information Delivery Portal or the Stored Process Web App. In which case, the alternate method of using ODS is OK but (see #2)

2) Usually, if you are using an ODS invocation statement, you also use FILE=_WEBOUT or BODY=_WEBOUT. So, if you are going to remove %STPBEGIN/%STPEND and you are going to leave in the call to STPSRV_HEADER, then I would recommend that you alter your code to be:

  

data _null_;

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

rc= stpsrv_header('Content-disposition', 'attachment;

filename= muliple sheets.xls');

run;

     

ods tagsets.ExcelXP file=_webout options(sheet_name="REP1");

** proc report 1;

ods tagsets.ExcelXP options(sheet_name="REP2");

** proc report 2;

ods tagsets.ExcelXP close;

You posted almost the same question, only about using TAGSETS.CSVALL to a different forum. Which result type of output do you really want? Have you searched for examples of using TAGSETS.EXCELXP in a stored process? There are many complete code examples posted to this forum. How are you submitting this stored process? Using the IDP or using the Stored Process Web App? Once you use STPSRV_HEADER, you are essentially creating a stored process that can only be run using client applications that can receive "streaming" output. So, for example, your posted stored process using STPSRV_HEADER would not work in Web Report Studio or in Powerpoint; nor is it likely to work in the Add-in for Microsoft Office using Excel. By default, a stored process submitted from within the Add-in for Microsoft Office using Excel can only "receive" SASReport XML, HTML or CSV results.

One other reason for not using both %STPBEGIN -and- STPSRV_HEADER is that %STPBEGIN issues one kind of content-type header and your STPSRV_HEADER issues another kind. If %STPBEGIN sent a content-type for text/html, then your "stream" would be received as an HTML stream, which would go into one worksheet and your %STPSRV_HEADER would essentially be ignored.

If you opened a track with Tech Support on this issue, they could help you capture your SAS log from this run and they could examine your log for errors or warning messages. Until you correct your stored process code, you will continue to get unreliable results when you run your stored process.

cynthia

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 2245 views
  • 0 likes
  • 2 in conversation