BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sarojkumar
Calcite | Level 5

Hi,

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

Regards,

Saroj

1 ACCEPTED SOLUTION

Accepted Solutions
verne
Calcite | Level 5

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

4 REPLIES 4
RichardinOz
Quartz | Level 8

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
Calcite | Level 5

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
Quartz | Level 8

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
Calcite | Level 5

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.

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
  • 4 replies
  • 3077 views
  • 3 likes
  • 3 in conversation