DATA Step, Macro, Functions and more

Libname XLSX on Linux stability

Accepted Solution Solved
Reply
Super User
Super User
Posts: 9,402
Accepted Solution

Libname XLSX on Linux stability

Am having a strange issue with libname xlsx which I wanted to see if anyone else was having. 

Scenario is, when creating an xlsx in SAS 9.4.1.M3 on linux (and then transferring it to windows), using the following code:

libname tmp xlsx "test.xlsx";
proc copy in=sashelp out=tmp memtype=data;
  select class;
run;
libname tmp clear;

I get an xlsx file, which is fine, and I can send the file via email also.  However when I include another dataset, say:

libname tmp xlsx "&_SASWS_./MUNDIPHARMA/test.xlsx";
data c1 c2; set sashelp.class; run;
proc copy in=work out=tmp memtype=data;
  select c1 c2;
run;
libname tmp clear;

This is the warning returned by the email system:

Microsoft Forefront Protection for Exchange Server removed a file since it was found to be infected.
File name: "winmail.dat->test (1).xlsx"
Malware name: "CorruptedCompressedFile"

So it seems that the xlsx engine works fine when one sheet is created, but on multiple sheets it is being thrown out as invalid.  If I take the file which gets rejected, open it in Excel, and saveas, the file created from there works fine, and is larger than the original as well (expected with the extra excel information).  

 

So my question is, does anyone know of an issue with the above, known bug or anything?

 


Accepted Solutions
Solution
‎03-16-2018 11:45 AM
Valued Guide
Posts: 559

Re: Libname XLSX on Linux stability

Hi @RW9

 

I think this is not something withing the SAS. There are some discussions about this in Microsoft forums and seems like it may be a bug in Microsoft Forefront which is trying to identify the file. 

 

There is a setting which disable deleting of corrupted or multiparted archives.

 Forefront Server Security Administrator > Settings> General Options > Scanning

 checkbox name is "Delete Corrupted Compressed File"

 

For more info: https://social.technet.microsoft.com/Forums/forefront/en-US/dbfb3252-e0e6-4a29-8979-bfb5b5a36409/cur...

 

Thanks,
Suryakiran

View solution in original post


All Replies
Solution
‎03-16-2018 11:45 AM
Valued Guide
Posts: 559

Re: Libname XLSX on Linux stability

Hi @RW9

 

I think this is not something withing the SAS. There are some discussions about this in Microsoft forums and seems like it may be a bug in Microsoft Forefront which is trying to identify the file. 

 

There is a setting which disable deleting of corrupted or multiparted archives.

 Forefront Server Security Administrator > Settings> General Options > Scanning

 checkbox name is "Delete Corrupted Compressed File"

 

For more info: https://social.technet.microsoft.com/Forums/forefront/en-US/dbfb3252-e0e6-4a29-8979-bfb5b5a36409/cur...

 

Thanks,
Suryakiran
Super User
Super User
Posts: 9,402

Re: Libname XLSX on Linux stability

Posted in reply to SuryaKiran

Thanks, I will let our IT look at that then.

Community Manager
Posts: 3,379

Re: Libname XLSX on Linux stability

Bug/behavior aside, my preferred method for creating multiple sheets (or replacing a sheet in an existing workbook) is PROC EXPORT with DBMS=XLSX.  I think it's more of the well-worn path, and it just seems to work more smoothly.  LIBNAME XLSX is convenient for quick discovery of what's in a spreadsheet (and I love using it for that) -- as long as the data sets are well formed with data starting in A:1.  But as it requires an exclusive lock on the workbook file, I find it tricky to use for complex data operations.

Super User
Super User
Posts: 9,402

Re: Libname XLSX on Linux stability

Posted in reply to ChrisHemedinger

Thanks, I don't often use it so not an issue.  Will keep the dbms=xlsx option in mind.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 120 views
  • 5 likes
  • 3 in conversation