12-01-2017 03:05 PM
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.
12-01-2017 03:27 PM
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.
12-01-2017 04:15 PM
12-01-2017 06:01 PM
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.
12-01-2017 09:04 PM
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.
12-01-2017 09:12 PM
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: