Your SAS programs, embedded in web apps and elsewhere

EG- ODS Excel made into a Stored Process to export to Excel 9.4 M3

Accepted Solution Solved
Reply
Highlighted
Contributor CLE
Contributor
Posts: 21
Accepted Solution

EG- ODS Excel made into a Stored Process to export to Excel 9.4 M3

I've got the code to work the way that I want in EG.  When I create a stored process, it opens it in a report form and not Excel.

 

What I need: I need an ODS Excel file=example.xlsx to open in Excel when I run the web application stored process.  I have code to do it with a tagset.excelXP, but would like to use ODS Excel - if possible.

 

Your help is appreciated.

 

Code:

 

ods excel file='example.xlsx'

options(sheet_interval='none'

sheet_name='M1 > M2'

embedded_titles='yes'

embedded_footnotes='yes'

tab_color='purple'

start_at='2,4'

frozen_headers='yes'

autofilter='1-5'

);

 

title 'Shop Throughput Greater in Month 1 versus Month 2';

footnote 'Only displays where throughput units were greater in month 1 versus month 2.';

 

proc report data=work.descriptions

style(header) = {font_face="Arial" font_size=13pt bordercolor=black background=orange foreground=white}

style(column) = {background=white font_face="Arial" font_size=10pt}

style(summary) = {htmlstyle="border-top:3px solid;border-bottom:3px solid"};

 

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 'Shop Throughput Greater in Month2 versus Month1';

footnote 'Only displays where throughput units were greater in Month2 than month1 .';

 

proc report data=work.description2

style(header) = {font_face="Arial" font_size=13pt bordercolor=black background=orange foreground=white}

style(column) = {background=white font_face="Arial" font_size=10pt}

style(summary) = {htmlstyle="border-top:3px solid;border-bottom:3px solid"};

 

run;

ods excel close;


Accepted Solutions
Solution
‎08-24-2017 12:15 PM
SAS Super FREQ
Posts: 302

Re: EG- ODS Excel made into a Stored Process to export to Excel 9.4 M3

Are you sure that the STPBEGIN/STPEND macros are not used?  SAS Enterprise Guide sometimes includes them on your behalf.

 

The gibberish that you see is the binary Excel XLSX file being displayed as text, because the browser didn't receive the Content-type header.  This can happen if STPBEGIN writes to _WEBOUT (or more accurately, ODS) before the DATA step writes the header, or if there is a syntax error in the DATA step that prevents the header being sent to the browser.

 

Vince DelGobbo

SAS R&D

View solution in original post


All Replies
SAS Super FREQ
Posts: 302

Re: EG- ODS Excel made into a Stored Process to export to Excel 9.4 M3

Try this code to see if it gives you what you want:

 

ods _all_ close;

data _null_;
rc = appsrv_header('Content-type', 'application/vnd.ms-excel');
rc = appsrv_header('Content-disposition','attachment; filename="example.xlsx"');
run;

ods Excel file=_webout;
  proc print data=sashelp.class; run; quit;
ods Excel close;

 

Do not use the STPBEGIN/STPEND macros; only use the code above.

 

Vince DelGobbo

SAS R&D

PROC Star
Posts: 1,291

Re: EG- ODS Excel made into a Stored Process to export to Excel 9.4 M3

Hi @Vince_SAS.  Do you expect %STPBEGIN to be extended to handle ODS EXCEL?

 

For a quick hack, I updated a local version so that it would not add the path option:

%*;
%*  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) /*I added this line*/
%then %do;

  %if (%qcmpres(&_TMPCAT) eq )
    %then %let ODSOPT=&ODSOPT path=WORK._TEMP_;
    %else %let ODSOPT=&ODSOPT path=&_TMPCAT;

  %if (&REPLAYFLAG EQ 1)
    %then %let ODSOPT=&ODSOPT (url=&_REPLAY);
    %else %let ODSOPT=&ODSOPT (url=NONE);

  %if (&CHARSETFLAG eq 0)
    %then %let ODSOPT=&ODSOPT CHARSET=' ';
%end;

 

That seems to get it working for my limited use case.  But I'd imagine for all the stuff that %STPBEGIN does, there would need to be plenty more updates. 

 

I don't really like the idea of me mucking around with the definitions of macros provided by SAS.  Would be great to see %STPBEGIN() extended to support the newer ODS destinations.

 

Thanks,

--Q.

SAS Super FREQ
Posts: 302

Re: EG- ODS Excel made into a Stored Process to export to Excel 9.4 M3

Support for the Excel and PowerPoint destinations has been added to the STPBEGIN and STPEND macros, and will be available starting with the upcoming fifth maintenance release for SAS 9.4.

 

Vince DelGobbo

SAS R&D

PROC Star
Posts: 1,291

Re: EG- ODS Excel made into a Stored Process to export to Excel 9.4 M3

Glad to hear that 9.4M5 versions of STPBEGIN and STPEND will support the new destinations! Will add a check in my hacked version to have it self-destruct when run on M5 or later. We're finally upgrading our server to M4 next week. Hopefully we won't be too far behind the curve after M5 comes out.
Contributor CLE
Contributor
Posts: 21

Re: EG- ODS Excel made into a Stored Process to export to Excel 9.4 M3

No, it doesn't work.  It displays a bunch of jibberish on the output screen - not in Excel.

Solution
‎08-24-2017 12:15 PM
SAS Super FREQ
Posts: 302

Re: EG- ODS Excel made into a Stored Process to export to Excel 9.4 M3

Are you sure that the STPBEGIN/STPEND macros are not used?  SAS Enterprise Guide sometimes includes them on your behalf.

 

The gibberish that you see is the binary Excel XLSX file being displayed as text, because the browser didn't receive the Content-type header.  This can happen if STPBEGIN writes to _WEBOUT (or more accurately, ODS) before the DATA step writes the header, or if there is a syntax error in the DATA step that prevents the header being sent to the browser.

 

Vince DelGobbo

SAS R&D

Contributor CLE
Contributor
Posts: 21

Re: EG- ODS Excel made into a Stored Process to export to Excel 9.4 M3

Yes, turning off the auto %stpbegin/%stpend during the stp generation did make your code work.  However, when I merge with my code - I get the file is corrupted.  Can I still assign the options to the _webout line?

Contributor CLE
Contributor
Posts: 21

Re: EG- ODS Excel made into a Stored Process to export to Excel 9.4 M3

Figured out what I did wrong!  Thank you so much.  It is working beautifully now.

 

 

SAS Super FREQ
Posts: 302

Re: EG- ODS Excel made into a Stored Process to export to Excel 9.4 M3

You're welcome!  I was just about to reply stating that your code worked for me using the SASHELP.CLASS table in both instances of PROC REPORT.  

 

Was it due to bad data?

 

Vince DelGobbo

SAS R&D

Contributor CLE
Contributor
Posts: 21

Re: EG- ODS Excel made into a Stored Process to export to Excel 9.4 M3

Appeared to be a syntex error that it wasn't giving me an error message - just a corrupt file message. I had deleted the style(summary) = line and forgot to add the semicolon at the end of the style(column) line.  The real code has extensive formatting.  I tried to only display a limited snippet here.  

☑ This topic is solved.

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

Discussion stats
  • 10 replies
  • 377 views
  • 6 likes
  • 3 in conversation