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

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

Sarojkumar — Thu, 15 Nov 2012 08:30:43 GMT

Hi,

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

Regards,

Saroj

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

RichardinOz — Thu, 15 Nov 2012 09:06:25 GMT

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

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

Sarojkumar — Thu, 15 Nov 2012 11:37:53 GMT

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.

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

RichardinOz — Thu, 15 Nov 2012 12:05:08 GMT

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

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

verne — Thu, 15 Nov 2012 16:30:54 GMT

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.