xml to xlsx

[Yan]

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?

[RW9]

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.

[Reeza]

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.

[Yan]

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.

[RW9]

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.

[Peter_C]

Is your EG server on windows?

I think that vb script might not work on unix