DATA Step, Macro, Functions and more

SAS2XLSX Generated XLSX Under Linux x64 Not Able to Open

Reply
Contributor
Posts: 29

SAS2XLSX Generated XLSX Under Linux x64 Not Able to Open

[ Edited ]

I have downloaded SAS2XLSX macro from PhUSEwiki and PhUSE Paper. I run this macro under PC SAS 9.3 x64 and Linux SAS 9.3 x64. But Linux generated XLSX is not working. I need to use it for Linux. I have also compared all individual XML files with PC SAS generated one, everything matched exactly. Still I am not able to open Linux generated XLSX file. As this paper suggest same carriage return hexadecimal code '0D'x I am using.

 

I have also attached both SAS generated XLSX files.

 

Could you please help me?

 

P.S: ODS EXCEL not supported by SAS 9.3

Super User
Super User
Posts: 9,415

Re: SAS2XLSX Generated XLSX Under Linux x64 Not Able to Open

Question 1: Do you have Office installed on your Linux, note the answer is no!  So how do you expect to "open the file on linux"?

 

Linux does not have Office software, hence you can't open it.  You might try using LibreOffice or OpenOffice, but my suggestion would be to go with an open non-proprietary format which is cross-platform and open text.  Depending on what you need the output for:

1) Output review files - RTF, maybe HTML

2) Data transfer - CSV or XML

 

Contributor
Posts: 29

Re: SAS2XLSX Generated XLSX Under Linux x64 Not Able to Open

I have generated in Linux and Opening in Windows 10 and Windows has office 2016. Did you try to open attached excel files in your system? Modern excel file is a ZIP file which contains standard XML files. Change XLSX extension to ZIP and try to open.
Super User
Super User
Posts: 9,415

Re: SAS2XLSX Generated XLSX Under Linux x64 Not Able to Open

Yes, am fully aware of what xlsx files are, but you did not mention opening on Windows, hence why I point this out.

 

This is unfortunately the pitfalls of using code downloaded from somewhere, and a major problem with the open source arena in general - knowing what the code does and being able to validate it.  I see that @KurtBremser has answered your question. 

I personally would say avoid using some third party script, if you need XML gemerated on Linux for use on a Windows Office (and doesn't it even sound daft), then use ods tagsets.excelxp.  Open Office is, at its core, a bit of a mess, an attempt to move towards open source.  

tagsets.excelxp creates plain text one file XML, which can be read and interpreted by Excel amongst other applications.

 

Super User
Posts: 9,888

Re: SAS2XLSX Generated XLSX Under Linux x64 Not Able to Open

The Linux version has a directory _tempxlsx in it, while the PC version has the contents to this directory at its root. So it looks that the zip was executed from the wrong directory.

After fixing that and running the macro with the necessary adaptations for AIX, the XML looks correct (although it created 2 header lines in the worksheet), and Excel 2013 could open it. LibreOffice coughed up on it, which is a bad indicator, as it means the xlsx does not conform to the publicly documented standard for xlsx files.

Bottom line: either upgrade to SAS 9.4, so you can use libname excel or ods excel, or use a simple textual format for data transfer. The second option is of course the preferred one, as it is stable and reliable. Which should not be expected from any MS file formats.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 4 replies
  • 136 views
  • 1 like
  • 3 in conversation