BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

I've tried to use ODS and EXCEL in the Stored Process Web Application (SAS 9.4 M2).

Unfortunately this code:

 

%global  _ODSDEST _ODSOPTIONS _ODSSTYLE;
*ProcessBody;
data _null_;
    rc = appsrv_header('Content-type','application/vnd.ms-excel');
    rc = stpsrv_header('Content-disposition','attachment; filename=test.xlsx');
run;

%let _ODSDEST=EXCEL;
%let _ODSSTYLE=Plateau;

%macro EXPORT;
    %stpbegin;
        proc print noobs label data=sashelp.TOURISM; run;
    %stpend;
%mend;

%EXPORT;

produce error:

 

- 66          STYLE=Plateau nogtitle nogfootnote body=_WEBOUT path=APSWORK.TCAT0000
-                                                ____                                ___
-                                                22                                  22
-                                                202                                 76
- 66       ! (url="/SASStoredProcess/do?_sessionid=00000000-0000-0000-0000-000000000000&_program=replay&_entry=APSWORK.TCAT0000.") CHARSET=' '
- ERROR 22-322: Syntax error, expecting one of the following: ;, AUTHOR, BOX_SIZING, CATEGORY, CLOSE, COMMENTS, CSSSTYLE, DOM, DPI, FILE, GFOOTNOTE, GTITLE, IMAGE_DPI, KEYWORDS, NOGFOOTNOTE, 
-               NOGTITLE, OPTIONS, SASDATE, STATUS, STYLE, TITLE, WORK.  
- 
- ERROR 202-322: The option or parameter is not recognized and will be ignored.
- ERROR 76-322: Syntax error, statement will be ignored.
- 
- WARNING: No output destinations active.

Problem is that there's no BODY in statement ODS EXCEL.

 

Is it possible use ODS EXCEL in Web Store Process? Any suggestions?

1 ACCEPTED SOLUTION

Accepted Solutions
pszumny
SAS Employee

Thanks for Your suggestions. I've found solution. Here's working code:

*ProcessBody;

data _null_;
	rc = stpsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	rc = stpsrv_header('Content-disposition','attachment; filename=test.xlsx');
run;

ods excel file = _webout ;
ods excel style = Plateau;

proc print noobs label data=sashelp.TOURISM; run;

ods excel close;

This store process is invoking by Stored Process Web App.

View solution in original post

5 REPLIES 5
Haikuo
Onyx | Level 15

Capture_stp.PNGI have never tried to code STP(Stored Process) from scratch, so I can't really comment on your code part.  From my base programmer background, it is easier to write the code part in EG program editor first, then convert it into STP. The following is my test code on ODS Excel, after converting to STP, it works (honestly I never thought it woundn't)

FILENAME OT "/share/test/test_%SYSFUNC(TODAY(),DATE9.).XLSX";

ODS EXCEL FILE = OT ;
PROC PRINT DATA=SASHELP.CLASS;
RUN;

ODS GRAPHICS OFF;
ODS EXCEL CLOSE;

I tried to run the above STP from numerous Apps (STP web app, Web report studio, information delivery portal, EG etc), it all seems normal to me.

 

Cynthia_sas
SAS Super FREQ

Hi:

   I have 2 suggestions: 1) post this question in the Stored Process forum and 2) review this Tech Support note: http://support.sas.com/kb/32/634.html. Notice that in the Tech Support note, they use the _WEBOUT form of the stored process invocation and they do NOT use %STPBEGIN/%STPEND. So aside from any problems of the fact that you did not say WHERE you were invoking this stored process (EG, Web Report Studio, Add-in for Office, Info Delivery Portal, Stored Process Web App), you need to be aware of the fact that only certain types of output can be returned to the various client applications. Generally, Excel can only receive CSV, HTML or SASReportXML output from a stored process. You would need to check with Tech Support about whether the "flavor" of .XLSX file XML that ODS EXCEL creates can even be returned to Excel in a Stored Process. 

 

  It used to be that for ODS TAGSETS.EXCELXP the only client applications that could receive the Spreadsheet Markup Language XML were the Stored Process Web App and the Information Delivery Portal.

 

  You might want to search for previous forum postings in the Stored Process Forum, because I know there have been other postings about using _WEBOUT and the STPSRV_HEADER functions to change the content-type header.

 

cynthia

pszumny
SAS Employee

Thanks for Your suggestions. I've found solution. Here's working code:

*ProcessBody;

data _null_;
	rc = stpsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	rc = stpsrv_header('Content-disposition','attachment; filename=test.xlsx');
run;

ods excel file = _webout ;
ods excel style = Plateau;

proc print noobs label data=sashelp.TOURISM; run;

ods excel close;

This store process is invoking by Stored Process Web App.

CLE
Obsidian | Level 7 CLE
Obsidian | Level 7

SAS9.4 M3

I'm trying to use your logic. It was created in EG then generated a stored process. When I run the stored process, it opens on the screen but not in Excel. It should open in Excel with multiple worksheets as it does in EG.

 

Did you drop the %global statements the original poster was using? I'm trying to figure out why your logic doesn't work for me.

Result capabilities in Stored Process are set to Stream and Package.  I'm needing to use on a portal page.  

 

Any help is appreciated. 

 

proc format;
value yaa .=' '
	  other=[comma10.];
run;
*ProcessBody;
data _null_;
rc = stpsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
rc = stpsrv_header('Content-disposition','attachment;filename=result.xlsx');
run;


ods excel file= _webout 
options(sheet_interval='none'
		sheet_name='M1 > M2'
		embedded_titles='yes' 
		embedded_footnotes='yes' 
		tab_color='purple' 
		start_at='1,1' 
		frozen_headers='yes' 
		autofilter='1-5' 
		zoom="90");

title justify=left font="Arial" height=14pt color=black bold 'Shop Throughput Greater in Month 1 versus Month 2';
footnote justify=left font="Arial" height=12pt color=black  'Only displaying where throughput units were greater in month 1 versus month 2.';
proc report data=work.descriptions nowd headline headskip spacing=1
style(header) = {font_face="Arial" font_size=13pt bordercolor=black background=orange foreground=white}
style(column) = {background=white font_face="Arial" font_size=10pt};

columns(CC Noun Description Rec_Code Tracking_Flag
		('_Month 1_' MONYY Qnew Sum_of_ORDR Sum_of_ORDR1)
		('_Month 2_' MONYY1 Qnew1 Sum_of_ORDR2 Sum_of_ORDR11)
		 Difference CMTS_Planning);
define CC /  order=data 'CPN' center style(column)=[cellwidth=85];
define NOUN /  'Noun' center style(column)=[cellwidth=125];
define Description /  'Description' center	style(column)=[cellwidth=200];
define 	Rec_Code /  'Resource/Code' center style(column)=[cellwidth=125];
define Tracking_Flag /  'Tracking / Flag' center style(column)=[cellwidth=125];
define MONYY / 'Month 1' display center 
		style(header)=[foreground=white background=lightblue] 
		style(column)=[cellwidth=90] ;
define Qnew / 'TP' display center 
		style(header)=[foreground=white background=lightblue] 
		style(column)=[cellwidth=85];
define Sum_of_ORDR / 'Bypass/ AVSH' display center format=yaa. 
		style(header)=[foreground=white background=lightblue] 
		style(column)=[cellwidth=100];
define Sum_of_ORDR1 / ' Sent to/Vendor' display center format=yaa. 
		style(header)=[foreground=white background=lightblue] 
		style(column)=[cellwidth=100];
define MONYY1 / 'Month 2' display center 
		style(header)=[foreground=white background=lightgreen]
		style(column)=[cellwidth=90];
define Qnew1 / 'TP' display center 
		style(header)=[foreground=white background=lightgreen]
		style(column)=[cellwidth=85];
define Sum_of_ORDR2 / 'Bypass' display center format=yaa. 
		style(header)=[foreground=white background=lightgreen]
		style(column)=[cellwidth=100];
define Sum_of_ORDR11 / 'Sent to/Vendor' display center format=yaa. 
		style(header)=[foreground=white background=lightgreen]
		style(column)=[cellwidth=100];
define Difference / 'TP Quantity/ Month 1 > Month 2 Comparison' display center
		style(column)=[cellwidth=160];
define CMTS_Planning / 'Planner /Comments' display left 
		style(column)=[cellwidth=500];
run;

ods excel options(sheet_interval='output');

ods exclude all;
data _null_;
	declare odsout obj();
	run;
ods select all;

ods excel options(sheet_interval='none' sheet_name='M2 > M1' tab_color='orange' );

title justify=left font="Arial" height=14pt color=black bold 'Shop Throughput Greater in Month 2 versus Month 1';
footnote justify=left font="Arial" height=12pt color=black 'Only displaying where throughput units were greater in Month 2 than Month 1 .';
proc report data=work.description2 nowd headline headskip spacing=1
style(header) = {font_face="Arial" font_size=13pt bordercolor=black background=orange foreground=white}
style(column) = {background=white font_face="Arial" font_size=10pt};

columns(CC Noun Description Rec_Code Tracking_Flag
		('_Month 1_' MONYY Qnew Sum_of_ORDR Sum_of_ORDR1)
		('_Month 2_' MONYY1 Qnew1 Sum_of_ORDR2 Sum_of_ORDR11)
		 Difference CMTS_Planning);
define CC /  order=data 'CPN' center style(column)=[cellwidth=85];
define NOUN /  'Noun' center style(column)=[cellwidth=125];
define Description /  'Description' center	style(column)=[cellwidth=200];
define 	Rec_Code /  'Resource/Code' center style(column)=[cellwidth=125];
define Tracking_Flag /  'Tracking / Flag' center style(column)=[cellwidth=125];
define MONYY / 'Month 1' display center style(header)=[foreground=white background=lightblue] 
		style(column)=[cellwidth=90] ;
define Qnew / 'TP' display center style(header)=[foreground=white background=lightblue] 
		style(column)=[cellwidth=85];
define Sum_of_ORDR / 'Bypass' display center format=yaa. 
		style(header)=[foreground=white background=lightblue] 
		style(column)=[cellwidth=100];
define Sum_of_ORDR1 / 'Sent to/Vendor' display center format=yaa. 
		style(header)=[foreground=white background=lightblue] 
		style(column)=[cellwidth=100];
define MONYY1 / 'Month 2' display center 
		style(header)=[foreground=white background=lightgreen]
		style(column)=[cellwidth=90];
define Qnew1 / 'TP' display center 
		style(header)=[foreground=white background=lightgreen]
		style(column)=[cellwidth=85];
define Sum_of_ORDR2 / 'Bypass' display center format=yaa. 
		style(header)=[foreground=white background=lightgreen]
		style(column)=[cellwidth=100];
define Sum_of_ORDR11 / ' Sent to/Vendor' display center format=yaa. 
		style(header)=[foreground=white background=lightgreen]
		style(column)=[cellwidth=100];
define Difference / 'TP Quantity/ Month 1 < Month 2 Comparison' display center
		style(column)=[cellwidth=160];
define CMTS_Planning / 'Planner /Comments' display left 
		style(column)=[cellwidth=500];
run;
ods excel close;

 

 

Quentin
Super User

I know it's an old post, but I agree it would be nice if %STPBEGIN() could be updated to know about the ODS EXCEL desitination.  I assume it would probably update this block:

 

%*;
%*  Map ODS destination to file type.  Use HTML as a
%*  default file type.
%*;

%if (%qupcase(%qcmpres(&_ODSDEST)) eq PDF) or
    (%qupcase(%qcmpres(&_ODSDEST)) eq PRINTER PDF) %then
  %let FILETYPE=pdf;
%else %if (%qupcase(%qcmpres(&_ODSDEST)) eq PS) or
          (%qupcase(%qcmpres(&_ODSDEST)) eq PRINTER PS) or
          (%qupcase(%qcmpres(&_ODSDEST)) eq PRINTER POSTSCRIPT) %then
  %let FILETYPE=ps;
%else %if (%qupcase(%qcmpres(&_ODSDEST)) eq CSV) or
          (%qupcase(%qcmpres(&_ODSDEST)) eq TAGSETS.CSV) or
          (%qupcase(%qcmpres(&_ODSDEST)) eq CSVALL) or
          (%qupcase(%qcmpres(&_ODSDEST)) eq TAGSETS.CSVALL) or
          (%qupcase(%qcmpres(&_ODSDEST)) eq TAGSETS.CSVBYLINE) %then
  %let FILETYPE=csv;
%else %if (%qupcase(%qcmpres(&_ODSDEST)) eq XML) or
          (%index(%qupcase(&_ODSDEST),SASREPORT) eq 1) or
          (%index(%qupcase(&_ODSDEST),TAGSETS.SASREPORT) eq 1) %then
  %let FILETYPE=xml;
%else %if (%qupcase(%qcmpres(&_ODSDEST)) eq RTF) %then
  %let FILETYPE=rtf;
%else %if (%qupcase(%qcmpres(&_ODSDEST)) eq WML) or
          (%qupcase(%qcmpres(&_ODSDEST)) eq TAGSETS.WML) %then
  %let FILETYPE=wml;
%else %if (%qupcase(%qcmpres(&_ODSDEST)) eq LATEX) or
          (%qupcase(%qcmpres(&_ODSDEST)) eq TAGSETS.LATEX) %then
  %let FILETYPE=tex;
%else
  %let FILETYPE=html;

 

Of course the beauty of it being an autocall macro is that you're free to muck with it yourself (at your own risk : ).  My use case was silghtly different, but had similar problem of %STPBEGIN() adding path= which caused ODS EXCEL statement to error. 

 

Not wanting to change much, I only updated the section of %STPBEGIN() which adds the path option so it doesn't do it when &_ODSDEST=EXCEL

%*;
%*  Set the PATH option for streaming if
%*  not already set by caller.
%*;

%if (%qupcase(%qsubstr(%qcmpres(&_RESULT),1,6)) eq STREAM) and
    (&PATHFLAG eq 0) and
    (%qupcase(%qcmpres(&FILETYPE)) ne PS) and
    (%qupcase(%qcmpres(&FILETYPE)) ne PDF)
    and (%qupcase(%qcmpres(&_ODSDEST)) ne EXCEL) /* new line*/
%then %do;
BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

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
  • 5 replies
  • 4421 views
  • 0 likes
  • 5 in conversation