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

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

CLE — Tue, 22 Aug 2017 15:15:56 GMT

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;

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

Vince_SAS — Thu, 24 Aug 2017 11:43:22 GMT

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

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

Quentin — Thu, 24 Aug 2017 13:08:06 GMT

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.

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

Vince_SAS — Thu, 24 Aug 2017 14:32:48 GMT

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

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

CLE — Thu, 24 Aug 2017 14:41:25 GMT

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

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

Vince_SAS — Thu, 24 Aug 2017 14:46:52 GMT

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

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

CLE — Thu, 24 Aug 2017 15:23:58 GMT

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?

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

CLE — Thu, 24 Aug 2017 16:15:18 GMT

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

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

Vince_SAS — Thu, 24 Aug 2017 16:18:52 GMT

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

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

CLE — Thu, 24 Aug 2017 16:24:20 GMT

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.

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

Quentin — Thu, 24 Aug 2017 18:25:34 GMT

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.