topic Re: xml to xlsx in ODS and Base Reporting

xml to xlsx

Yan — Mon, 07 Jul 2014 12:55:06 GMT

I am trying to convert the xml file created by ods tagsets.excel to .xlsx file. Found codes here http://support.sas.com/kb/43/496.html. Basically it creates a vb script in the data step then call it. It works perfectly in base SAS but the CALL SYSTEM seems not work with SAS EG. Is there other way to execute the VB Script on SAS EG?

Re: xml to xlsx

RW9 — Mon, 07 Jul 2014 12:58:40 GMT

Sorry, what are you trying to convert the XML to? Excel will quite happily parse and open XML files. As for running VB scripts from SAS, that depends a lot on security permissions, drive mappings etc. If you really have to have the file in Excel binary form, then try proc export, although you lose some control. Alternatively open the file in Excel and save it.

Re: xml to xlsx

Reeza — Mon, 07 Jul 2014 15:38:11 GMT

EG has X commands turned off by default on the install. You can ask your IT to turn it on or try the following:

http://blogs.sas.com/content/sasdummy/2007/10/05/you-are-under-my-command-prompt/

I haven't tried it, and only came across it last week, so interested to hear if it works for you

I've been finding I need admin rights to add Add-Ins for EG anyways so still have to talk to IT anyway.

Re: xml to xlsx

Yan — Mon, 07 Jul 2014 19:20:29 GMT

I am trying to convert the xml output to excel files. The person requesting the reports wants it to be in the xlsx extension even though xml can be opened by excel too. And I am trying to automate the report generation and get the conversion done by codes. Will talk to our IT people to see if it's possible to trun on the X command on EG. Thanks for the suggestion.

Re: xml to xlsx

RW9 — Mon, 07 Jul 2014 20:19:15 GMT

The Excel .xlsx format is XML. If you right click on a an Excel generated .XLSX file and rename the file extension to .ZIP you will now be able to explore the XML contents of a zip file. You can name the file .XSLX in your ods output statement though Excel will complain its not in its exact format and it will process it fine. What you are talking about - the Excel prorietary binary file which used to be used by Office products is no longer the standard output, the XML based one is. Hence the old formats are there for backwards compatibility purposes. You do have other options on your output depending on the specific requirements, delimited files e.g. CSV is a good transport format if you need to send data to someone, Excel will also automatically open and process this. You can still use proc export which will generate a binary file per the old Excel standard. Another alternative is to move your VBA script into an Excel file itself as a macro and run it from there. So myriads of options depending on the output.

Re: xml to xlsx

Peter_C — Mon, 07 Jul 2014 21:26:25 GMT

Is your EG server on windows?

I think that vb script might not work on unix