BookmarkSubscribeRSS Feed
ncsthbell
Quartz | Level 8

I am on SAS 9.2 and have written a program that uses ODS Tagset.exelxp and creates 4 separate worksheets contained within one excel spreadsheet. I am use 'Proc Report' to create the multiple worksheets.   When run from the SAS Portal, the output is in '.xml' format which when opened looks like an excel spreadsheet with 4 worksheets(tabs).  This is working correctly.    However, the user does not want to run it from the portal so I am having to create it as a SAS batch job to be scheduled. I  can't quite figure out how to change the output from going to an 'xml.' file and have the output go to a server file/path location and allow it to be opened as an excel spreadsheet with the multiple worksheets.

I have tried using the Export function but Excel is not an option in the output types.  Any recommendations on how I can accomplish this?

Thanks

3 REPLIES 3
ursula
Pyrite | Level 9

My understanding is you have export file by using ODS Tagset. exelxp.

after you got the .xml file, click on excel program, and open the .xml file, then SAVE it as excel file. this way, you have convert the .xml file into normal excel file.

Hope it helps!

ncsthbell
Quartz | Level 8

Ursula, you are correct & this works.  However, my user has decided they want this job scheduled as a batch job in SAS so now it will not be running on the Portal giving the user the option to 'open' or 'save' the output .xml file.   I  just edited my post with the info about running it as batch.

Cynthia_sas
SAS Super FREQ

Hi:

  Usually, in this situation, you have your batch job write the .xml files to a directory. Then, you have a VBScript that reads the directory, opens each file as XML and resaves it as an XLS or XLSX file. Once the file is resaved, you can "empty" the original directory, by sending the original output to archive or changing the file names or even deleting the original files. Then the directory is ready to receive the next night's output from the batch job.

  The only way to make proprietary Excel files is through the use of PROC EXPORT or the LIBNAME engine. If you use ODS, you are creating an ASCII text file (CSV, HTML or XML) that Excel knows how to open and render.

  The far easiest way to deal with this is to educate your users that it is perfectly acceptable in Excel to open an .XML file using FILE--> Open. On the Excel Open menu, they only have to look for a file type/extension of .XML Files  XMLIf they really want to see the .XLS extension, then teach them how to do the File --> Save AS. You can even show them that .XML is an acceptable file type to open (see screenshot).

cynthia


open_xml_inside_excel.png

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
  • 3 replies
  • 878 views
  • 0 likes
  • 3 in conversation