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

[Sarojkumar]

Hi,

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

Regards,

Saroj

[RichardinOz]

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

[Sarojkumar]

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.

[RichardinOz]

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

[verne · Accepted solution]

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.