BookmarkSubscribeRSS Feed
Rajaram
Obsidian | Level 7

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

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

 

Rajaram
Obsidian | Level 7
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.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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 @Kurt_Bremser 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.

 

Kurt_Bremser
Super User

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.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1265 views
  • 1 like
  • 3 in conversation