topic Re: How to convert xml files to xlsx in SAS EG 7.15 in SAS Programming

How to convert xml files to xlsx in SAS EG 7.15

Iske — Wed, 04 Aug 2021 16:51:48 GMT

I am trying to convert xml files to xlsx files in SAS EG v7.15 that is hosted on an AWS server.

The way the files are currently created is by using the following code below and a macro to name the files according to a list of agency names and numbers.

ods tagsets.excelxp
path = '/data//Test'
file="agency_report_&name._&agency..xml"

ods tagsets.excelxp close;
ods listing close;

I have seen previous post about users using the following code to convert all the files from xml to xlsx

options noxsync noxwait;
%convert_files(default=/data/Test,ext=xml);

However, when I do this I get the following three error statements

ERROR 13-12: Unrecognized SAS option name NOXSYNC.

ERROR 13-12: Unrecognized SAS option name NOXWAIT.

ERROR: Shell escape is not valid in this SAS session.

The macro %convert_files is run before I call it. Does anyone know why this is not working or if there is another way for me to convert these xml files to xlsx?

Re: How to convert xml files to xlsx in SAS EG 7.15

ballardw — Wed, 04 Aug 2021 17:13:48 GMT

Why? The setting of your session have disabled the X command and related options most likely.

This is typically set by admins of a SAS server install such as EG to prevent headaches caused by uncontrolled submission of operating system commands.

If you have the right license options I would be tempted to try:

ods excel
path = '/data//Test'
file="agency_report_&name._&agency..xlsx"
/*  I really hope you have code that goes here*/
ods excel close;

If you must have the XML and the XLSX you can have multiple ODS output destinations open at one time. Just make sure to close them appropriately.

I typically used a "balanced sandwich" approach to consistently open and close multiple destinations like:

Ods tagsets.excelxp <tagset options>;

Ods rtf  < rtf options>;

<output generating code goes here>

ods rtf close;

ods tagsets.excelxp close;

The order of the close bits shouldn't make a difference but I like to make sure first opened is last closed just to find the statements when needed.

Re: How to convert xml files to xlsx in SAS EG 7.15

Tom — Wed, 04 Aug 2021 17:25:55 GMT

Are you asking how to convert an XML files generated by TAGSETS.EXCELXP to an XSLX file?
The best way is to open the XML file with Excel itself and then save it as an XLSX file.

You didn't provide any code for the macro you are calling but from the error messages you are getting I suspect it is trying to do that. Since you are using Enterprise Guide to run your SAS code (instead of just running the SAS code directly using SAS itself) make sure the computer where SAS is running actually has Excel installed.

Re: How to convert xml files to xlsx in SAS EG 7.15

jimbarbour — Wed, 04 Aug 2021 18:26:47 GMT

Why not just go straight to Excel (xlsx)? Don't use the essentially obsolete ExcelXP tag set. Use the ODS Excel destination. What version of SAS are you running? Unless it's a fairly old version, you should be able to use the Excel destination. If you use the Excel destination, then you don't need to convert XML to XLSX; you'll have XLSX directly.

This paper might be helpful:

https://blogs.sas.com/content/sgf/2017/02/20/tips-for-using-the-ods-excel-destination/

Jim

Re: How to convert xml files to xlsx in SAS EG 7.15

Iske — Wed, 04 Aug 2021 19:37:50 GMT

No, I am asking after I have created this file as xml, how do automatically convert these files to xlsx. Since this process outputs about 250 files opening and saving as xlsx would be a lot of work for a monthly report. So I am trying to find a way to work around it.

The virtual desktop that I use for work does have Excel installed.

Re: How to convert xml files to xlsx in SAS EG 7.15

jimbarbour — Wed, 04 Aug 2021 20:30:02 GMT

@jimbarbour wrote:

Why not just go straight to Excel (xlsx)? Don't use the essentially obsolete ExcelXP tag set. Use the ODS Excel destination. What version of SAS are you running? Unless it's a fairly old version, you should be able to use the Excel destination. If you use the Excel destination, then you don't need to convert XML to XLSX; you'll have XLSX directly.

This paper might be helpful:

https://blogs.sas.com/content/sgf/2017/02/20/tips-for-using-the-ods-excel-destination/

Jim

You might consider my suggestion of just going directly to XLSX and eliminating the XML step. Going directly to XLSX can be fully automated.

Jim