03-28-2013 04:50 PM
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?
03-28-2013 04:57 PM
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!
03-28-2013 05:22 PM
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.
03-29-2013 09:18 AM
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).