topic Re: Convert XML to XSLX on Linux in SAS Programming

Convert XML to XSLX on Linux

SAS-Nutzer — Thu, 29 Apr 2021 13:58:23 GMT

Hi,

we have a routine that is creating a .xls file which is actually an XML file (via ods Tagsets.ExcelXP). Now I know there are ways in SAS to convert an XML-file to a xlsx-file on Windows, which is described here and here . However, those require SAS on Windows since they use a Visual Basic script. Our SAS runs on Linux. Is there any way to do a similar behaviour on SAS-Linux?

Re: Convert XML to XSLX on Linux

Kurt_Bremser — Thu, 29 Apr 2021 14:24:09 GMT

Why carry the church around the cross?

Create the file with ODS EXCEL in the first place.

Re: Convert XML to XSLX on Linux

ballardw — Thu, 29 Apr 2021 17:05:37 GMT

@Kurt_Bremser wrote:

Why carry the church around the cross?

Create the file with ODS EXCEL in the first place.

Too obvious.

May be an appropriate place to remind @SAS-Nutzer that you can send results to multiple ODS destinations at the same time with something like:

Ods tagsets.excelxp file="<path>\filename.xml";
Ods excel file="<path>\filename.xlsx";
ods rtf file="<path>\filename.rtf";

<something that generates output>

ods rtf close;
ods excel close;
ods tagsets.excelxp close.

BTW bad things happen to people that lie to the operating system. The Excelxp creates xml. naming the output with XLS extension does not change the file type and can cause problems related to "type does not match extension" which can be a serious issue as Microsoft office file formats get further away from the getting very old XLS extension binary format.

Re: Convert XML to XSLX on Linux

ChrisNZ — Thu, 29 Apr 2021 22:48:53 GMT

Ah! A German idiom! 👍

Quite self-explanatory, but I looked it up just in case

carry the church around the cross - be awkward, make an unnecessary detour.

according to the web

Re: Convert XML to XSLX on Linux

ChrisNZ — Thu, 29 Apr 2021 22:53:05 GMT

@Kurt_Bremser @ballardw

Maybe @SAS-Nutzer does not know about the excel ODS destination. These have come thick and disorganised, so keeping track is not necessarily easy.

Re: Convert XML to XSLX on Linux

Kurt_Bremser — Fri, 30 Apr 2021 06:33:14 GMT

@ChrisNZ wrote:

Ah! A German idiom! 👍

Quite self-explanatory, but I looked it up just in case

carry the church around the cross - be awkward, make an unnecessary detour.

according to the web

The Catholic church in Austria has several feasts where a procession through the village is done. As churches are usually situated at the village center, the cross is carried "around the church".

At least since the 18th century, people have used this phrase to denote something that is done unnecessarily complicated. And I found that a direct translation still gets the meaning across 😉

Re: Convert XML to XSLX on Linux

Ksharp — Fri, 30 Apr 2021 13:49:58 GMT

Can you use the following :

filename t temp;
libname x xmlv2 'c:\temp\temp.xml' xmlmap=t automap=replace;
proc copy in=x out=work noclone;
run;

to read in sas . and export excel file via sas as well ?