topic Re: ODS excel in ODS and Base Reporting

ODS excel

brendanb — Fri, 09 Oct 2020 16:35:59 GMT

Hi, I am working on Linux.

Trying to create a excel spreadsheet with multiple pages. I am running a stored proc via SAS ECM (using grid-action). The stored process essentially run a query and builds a few tables wit various structures, hence the need to write multiple sheets. I am then using _webout as a file destination to download the file to my machine. The procedure works fine with one table. The minute I add a second proc print I get an excel error stating that the format and extension of 'filename.xls' don't math. The file could be corrupt or unsafe. Unless you trust... I click yes to open and then it gives me three choices in an Open XML box, 1. As an XML table, 2. As a read-only Workbook, 3. Use the xml source pane. Any option fails to open the file. I have tried swapping content types, filename extensions, no luck. With the code below, if I choose just to print one table, I still get the format and extension message, but hen I click yes to open it works fine and I get the results. Your help would be appreciated. Thank you in advance. My code:

ods listing close; 
data _null_; 
rc = stpsrv_header('Content-type','application/vnd.ms-excel'); 
run; 

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

data _null_; 
rc = stpsrv_header('Content-disposition', "attachment; filename=subpoena_&caserk._txns.xls"); 
run; 

ODS excel FILE=_webout style=plateau OPTIONS(SHEET_NAME="Class"); proc print data=FITACCTSTXNS; 
run;quit; 

ODS excel FILE=_webout style=plateau OPTIONS(SHEET_NAME="Class2"); proc print data=PACS_057JTXNS; 
run;quit;

 ods excel close ;

Re: ODS excel

jimbarbour — Fri, 09 Oct 2020 16:53:12 GMT

Thank you for taking the time to post your code as SAS code. It makes it *so* much easier to read.

I've struggled mightily with ODS from time to time. ODS is wonderful and works great most of the time, but boy is it a pain when it does not work. What I've done in the past to correct problems like this when working on the older ExcelXP destination is to open up the generated code and manually examine it for errors, and in fact I found a number of them. I then used Proc Template and edited the template code for the destination to create work-arounds. Template code is SAS-like but isn't exactly SAS. It was a bit of a pain to work with, but I did successfully compensate for problems with merged cells and incorrectly formatted cells containing numbers defined as character.

Presumably you're generating XLSX files that you are able to open on a laptop or desktop Windows machine. XLSX files as I recall are really just zip files containing XML inside. I believe you can:

Rename the file from .xlsx to .zip
Unzip the file
Retrieve the XML

You would then have to look through the XML, manually, for problems. ODS is doing something wrong, and you just have to find it, at least that's been my experience. Vince DelGobbo (@Vince_SAS) and Chevell Parker (@Chevell_sas) are very knowledgeable SAS employees in this area. You may need to call upon their expertise for something like this, but I would at least try to take a look at the XML and see what you can find.

Good luck,

Jim

Re: ODS excel

Reeza — Fri, 09 Oct 2020 16:58:09 GMT

For the second ODS EXCEL statement try removing the FILE= specification, the prior one should still be valid.

Re: ODS excel

Vince_SAS — Fri, 09 Oct 2020 17:05:17 GMT

Try this (untested) code:

ods _all_ close;

data _null_; 
rc = stpsrv_header('Content-type','application/vnd.ms-excel'); 
rc = stpsrv_header('Content-disposition', "attachment; filename=subpoena_&caserk._txns.xlsx"); 
run; 

ods excel file=_webout style=plateau options(sheet_name="Class"); 

proc print data=FITACCTSTXNS; run; quit; 

ods excel options(sheet_name="Class2"); 

proc print data=PACS_057JTXNS; run; quit;

ods excel close;

Vince DelGobbo

SAS R&D

Re: ODS excel

brendanb — Fri, 09 Oct 2020 17:17:05 GMT

Thanks for the quick response and tips, will have a look at this. I am a novice with ODS, hardly ever use it 🙂 Appreciated.

Re: ODS excel

brendanb — Fri, 09 Oct 2020 17:22:10 GMT

thanks for the quick reply, i tried it, leaves me with just the one sheet.

Re: ODS excel

Reeza — Fri, 09 Oct 2020 17:23:06 GMT

Can you show the log from the code?

Re: ODS excel

brendanb — Fri, 09 Oct 2020 17:25:56 GMT

thanks for the help, i tried it, i only get the first print.

Re: ODS excel

Vince_SAS — Fri, 09 Oct 2020 17:30:44 GMT

Try replacing the first data set with SASHELP.CLASS and the second with SASHELP.RETAIL, and rerun the code.

Vince DelGobbo

SAS R&D

Re: ODS excel

brendanb — Fri, 09 Oct 2020 17:36:53 GMT

please see attached log.

Re: ODS excel

brendanb — Fri, 09 Oct 2020 17:40:18 GMT

tried that, noticing this error at the end i dont understand.

69 +
70 +
71 +ods listing close;
72 +data _null_; rc = stpsrv_header('Content-type','application/vnd.ms-excel'); run;

INFO: Character variables have defaulted to a length of 200 at the places given by: (Line):(Column). Truncation can result.
72:15 rc
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 774.62k
OS Memory 32672.00k
Timestamp 2020/10/09 07:37:58 PM
Step Count 8621 Switch Count 0
Page Faults 0
The SAS System

Page Reclaims 2
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

73 +data _null_; rc = stpsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); run;

INFO: Character variables have defaulted to a length of 200 at the places given by: (Line):(Column). Truncation can result.
73:15 rc
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 773.71k
OS Memory 32672.00k
Timestamp 2020/10/09 07:37:58 PM
Step Count 8622 Switch Count 0
Page Faults 0
Page Reclaims 2
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0

74 +
75 +
76 +data _null_; rc = stpsrv_header('Content-disposition', "attachment; filename=subpoena_&caserk._FITACCTSTXNS.xls"); run;

INFO: Character variables have defaulted to a length of 200 at the places given by: (Line):(Column). Truncation can result.
76:15 rc
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
The SAS System

system cpu time 0.00 seconds
memory 774.62k
OS Memory 32672.00k
Timestamp 2020/10/09 07:37:58 PM
Step Count 8623 Switch Count 0
Page Faults 0
Page Reclaims 3
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0

77 +ODS excel FILE=_webout style=plateau OPTIONS(SHEET_NAME="Class");
78 +proc print data=SASHELP.CLASS; run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.04 seconds
user cpu time 0.04 seconds
system cpu time 0.00 seconds
memory 1837.87k
OS Memory 40388.00k
Timestamp 2020/10/09 07:37:58 PM
Step Count 8624 Switch Count 0
Page Faults 0
Page Reclaims 317
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 7
Block Input Operations 0
Block Output Operations 0

78 !+ quit;
79 +
80 +ODS excel FILE=_webout OPTIONS(SHEET_NAME="Class2");
The SAS System

NOTE: Writing EXCEL Body file: _WEBOUT
81 +proc print data=SASHELP.RETAIL; run;

NOTE: There were 58 observations read from the data set SASHELP.RETAIL.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.10 seconds
user cpu time 0.10 seconds
system cpu time 0.01 seconds
memory 1714.31k
OS Memory 41668.00k
Timestamp 2020/10/09 07:37:59 PM
Step Count 8625 Switch Count 0
Page Faults 0
Page Reclaims 374
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 9
Block Input Operations 0
Block Output Operations 0

81 !+ quit;
82 +
83 +ods excel close ;
NOTE: Writing EXCEL file: ./3F9A708B-5F2B-BD4C-AE18-EABD74948E86
ERROR: Physical file does not exist, /grid/config/Lev1/SASApp/3F9A708B-5F2B-BD4C-AE18-EABD74948E86.
84 +
85 +
86 +* Begin EG generated code (do not edit this line);
87 +;*';*";*/;quit;
88 +

Re: ODS excel

Reeza — Fri, 09 Oct 2020 17:40:50 GMT

That shows you still left the second _webout portion in your code (line 80 in the log). You need to remove the red part was the suggested solution.

ODS excel FILE=_webout OPTIONS(SHEET_NAME="Class2");

Re: ODS excel

Vince_SAS — Fri, 09 Oct 2020 17:42:03 GMT

You didn't follow my code pattern; you still have FILE= on the second ODS statement, so you only get the last output.

Vince DelGobbo

SAS R&D

Re: ODS excel

Vince_SAS — Fri, 09 Oct 2020 17:44:40 GMT

Try adding a LENGTH statemement:

length rc $32767;

Vince DelGobbo

SAS R&D

Re: ODS excel

brendanb — Fri, 09 Oct 2020 17:50:29 GMT

sorry meant to say that log was with both webouts. it worked, awesome !!!!! thanks you. Not sure why it did not work the first time around.

ods listing close;
data _null_; rc = stpsrv_header('Content-type','application/vnd.ms-excel'); run;
data _null_; rc = stpsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); run;

data _null_; rc = stpsrv_header('Content-disposition', "attachment; filename=subpoena_&caserk._FITACCTSTXNS.xls"); run;
ODS excel FILE=_webout style=plateau OPTIONS(SHEET_NAME="Class");
proc print data=NEPS_TXN; run;quit;

ODS excel OPTIONS(SHEET_NAME="Class2");
proc print data=FITACCTSTXNS; run;quit;

ods excel close ;

will do some more testsing.

Re: ODS excel

brendanb — Fri, 09 Oct 2020 18:04:30 GMT

Hi, thanks for the help, I did what you said and it worked. did some more tests and it works like a dream! Thank you again, you have no idea how i struggled with this.

Re: ODS excel

brendanb — Fri, 09 Oct 2020 18:05:29 GMT

This works, thanks, thought I could set two replies as a solution.

Re: ODS excel

Vince_SAS — Fri, 09 Oct 2020 18:18:18 GMT

My papers might help you when working with Excel:

https://support.sas.com/rnd/papers/intro-multisheet-excel-with-sas/index.html

The "ExcelXP Tagset Paper Index" link on that page might also be helpful.

Vince DelGobbo

SAS R&D