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?
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...
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, I will let our IT look at that then.
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.
Thanks, I don't often use it so not an issue. Will keep the dbms=xlsx option in mind.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.