ODS excel

[brendanb]

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 ;

[jimbarbour]

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:

1. Rename the file from .xlsx to .zip
2. Unzip the file
3. 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

[brendanb]

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

[Vince_SAS]

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

[Reeza · Accepted solution]

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

[brendanb]

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

[Reeza]

Can you show the log from the code?

[brendanb]

please see attached log.

[Reeza]

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");

[brendanb]

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.

[Vince_SAS]

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

[brendanb]

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.

[Vince_SAS]

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

[brendanb]

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

[Vince_SAS]

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

Vince DelGobbo

SAS R&D