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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Vince_SAS
Rhodochrosite | Level 12

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

10 REPLIES 10
Vince_SAS
Rhodochrosite | Level 12

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

Quentin
Super User

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.

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.
Vince_SAS
Rhodochrosite | Level 12

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

Quentin
Super User
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.
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.
CLE
Obsidian | Level 7 CLE
Obsidian | Level 7

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

Vince_SAS
Rhodochrosite | Level 12

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

CLE
Obsidian | Level 7 CLE
Obsidian | Level 7

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?

CLE
Obsidian | Level 7 CLE
Obsidian | Level 7

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

 

 

Vince_SAS
Rhodochrosite | Level 12

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

CLE
Obsidian | Level 7 CLE
Obsidian | Level 7

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.  

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
  • 10 replies
  • 2920 views
  • 7 likes
  • 3 in conversation