topic Re: Does ODS EXCEL work on mainframe / z/OS under V9R4M2 ? in ODS and Base Reporting

Does ODS EXCEL work on mainframe / z/OS under V9R4M2 ?

David_Gelb — Fri, 01 Dec 2017 20:05:57 GMT

I understand that ODS EXCEL is still "experimental", but does it work at all on z/OS (mainframe)?

When I try to use it, I get an "Invalid physical name" upon executing ODS EXCEL close.

This happens when the destination file is either specified as a unix system services file,

or an old fashioned data set allocated as either RECFM=U or RECFM=VB.

I do not get such a message when trying to do something similar when using PC SAS 9.4 TS1M1,

and am able to use it to produce an .xlsx file with the contents of SASHELP.CLASS.

1    filename te1 '/tmp/tstx1.xlsx';
2    ods excel file=te1;
3    proc print data=sashelp.class;
4    run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The PROCEDURE PRINT used 0.09 CPU seconds.


5    ods excel close;
NOTE: Writing EXCEL file: /tmp/tstx1.xlsx
ERROR: Invalid physical name.

6    filename te1 'FIPDJG.TESTXLSX' disp=old;
7    ods excel file=te1;
8    proc print data=sashelp.class;
9    run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The PROCEDURE PRINT used 0.08 CPU seconds.


10   ods excel close;
NOTE: Writing EXCEL file: FIPDJG.TESTXLSX
ERROR: Invalid physical name.

Re: Does ODS EXCEL work on mainframe / z/OS under V9R4M2 ?

ballardw — Fri, 01 Dec 2017 20:27:05 GMT

I suggesting using a full path in the filename or file reference. Otherwise the output is likely to direct to a default location where SAS is executing and you are likely not to have permissions to write files there.

Since your second filename statement:

6 filename te1 'FIPDJG.TESTXLSX' disp=old;

does not have the /tmp/ at the beginning that is the most likely cause of the error message.

Re: Does ODS EXCEL work on mainframe / z/OS under V9R4M2 ?

David_Gelb — Fri, 01 Dec 2017 21:15:50 GMT

ballardw,
Thanks for your reply. both "/tmp/testxlsx.xlsx" and 'FIPDJG.TESTXLSX' are fully qualified file names or paths. The former is a USS path, while the latter is a traditional mainframe dataset.

Re: Does ODS EXCEL work on mainframe / z/OS under V9R4M2 ?

ballardw — Fri, 01 Dec 2017 23:01:48 GMT

@David_Gelb wrote:
ballardw,
Thanks for your reply. both "/tmp/testxlsx.xlsx" and 'FIPDJG.TESTXLSX' are fully qualified file names or paths. The former is a USS path, while the latter is a traditional mainframe dataset.

We may have a jargon difference. To me "fully qualified" means starting from a drive mount point or similar. With windows that means either start with a drive such as "C:\mypath\otherfolder\file.txt" or a server UNC reference. With Unix often that starts at /usr/...

It's been 30 years since I worked with an IBM mainframe and I don't remember writing to anything that wasn't defined in the JCL before the SAS code executed. But that was all batch submission so I have no clue what may have changed in that time period. I'm not sure the entire error information gets sent to the SAS log but may be in the job log from the OS.

Re: Does ODS EXCEL work on mainframe / z/OS under V9R4M2 ?

jimhorne — Sat, 02 Dec 2017 02:04:34 GMT

Try adding an OPTIONS FILESYSTEM=HFS; statement to your code. I forget what release of SAS I had to put that in to get it to recognize USS files. I won't guarantee that it will fix it but it is necessary for at least 9.4 or better.

Re: Does ODS EXCEL work on mainframe / z/OS under V9R4M2 ?

SASKiwi — Sat, 02 Dec 2017 02:12:54 GMT

Maybe this link will help. Its an old one but it does include examples of using the EXCELXP tagset on mainframes. If you can get these working it might work for ODS EXCEL:

http://support.sas.com/rnd/base/ods/templateFAQ/MVSODS3.pdf