DATA Step, Macro, Functions and more

How to create an excel sheet in Z/OS and place it in a desktop

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

How to create an excel sheet in Z/OS and place it in a desktop

Hi,

How to create a excel sheet from a mainframe VSAM file and place that excel in a PC.

Regards,

Saroj


Accepted Solutions
Solution
‎11-15-2012 11:30 AM
N/A
Posts: 1

Re: How to create an excel sheet in Z/OS and place it in a desktop

You could also use the EXCELXP tagset to create an XML file.  That would give you more flexibility than a simple CSV file.  It can also create multiple sheets in the workbook.  Then FTP it where you need it.  (I had to specify on FTP filename statement ENCODING=PCOEM437 to get it to work.)  XML files are really bloated so once you get it to your PC by doing a 'SAVE AS' XSLX will save a lot of space.

View solution in original post


All Replies
Super Contributor
Posts: 644

Re: How to create an excel sheet in Z/OS and place it in a desktop

You need to give us a little more information about your environment.  Presumably you have SAS licensed on at least one platform.  Tell us more.

Otherwise all we can say is to create a CSV file in a SAS datastep and download it (with appropriate EBCDIC conversion) to import into MS Excel. Or, if you are up to it, use ODBC to create a file in a format that Excel can open.

Richard in Oz

Occasional Contributor
Posts: 14

Re: How to create an excel sheet in Z/OS and place it in a desktop

SAS is installed in mainframe. The requirement is like - we have to create the output in excel file and to place it in C: drive. Would you like to know any more info.

Super Contributor
Posts: 644

Re: How to create an excel sheet in Z/OS and place it in a desktop

Unless you also have SAS installed on a PC and SAS Connect installed on both platforms, your simplest course would be the one I outlined.

Write a SAS datastep to read your VSAM data, then either use PROC EXPORT (if available on your platform) or a second datastep to write out all values as "comma separated values", ie as text fields with a comma between each.  The first line of the file should contain the column names, again separated by a comma.  Often, this line is the longest in the file and can be used to set LRECL on the output file.  Make sure that any dates are formatted as DATE9 or YYMMDD10, and timestamps are formatted as DATETIME20 at least, or TIME8 if there is no date information.

Next download this file to a Windows PC that has MS Office installed.  Make sure the EBCDIC to ASCII conversion takes place, and the file is saved with a .csv extension. In recent Microsoft installations, double clicking on a csv file will open it in MS Excel.  You can then check that the file has been correctly downloaded and pass it for user acceptance.

Note that this method will not apply any formatting to the the spreadsheet, nor any titles or pivot tables or charts.  If any of these are required, you may need a VBA programmer to create a destination workbook that will pick up data in the sheet you have created.

Richard in Oz

Solution
‎11-15-2012 11:30 AM
N/A
Posts: 1

Re: How to create an excel sheet in Z/OS and place it in a desktop

You could also use the EXCELXP tagset to create an XML file.  That would give you more flexibility than a simple CSV file.  It can also create multiple sheets in the workbook.  Then FTP it where you need it.  (I had to specify on FTP filename statement ENCODING=PCOEM437 to get it to work.)  XML files are really bloated so once you get it to your PC by doing a 'SAVE AS' XSLX will save a lot of space.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 724 views
  • 3 likes
  • 3 in conversation