BookmarkSubscribeRSS Feed
David_Gelb
Calcite | Level 5

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.

 

5 REPLIES 5
ballardw
Super User

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.

 

David_Gelb
Calcite | Level 5
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.
ballardw
Super User

@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.

jimhorne
Obsidian | Level 7

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.

SASKiwi
PROC Star

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

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
  • 5 replies
  • 2639 views
  • 1 like
  • 4 in conversation