Hi!
I do not use SAS very often and hope you can give me some tips.
The data must be exported from SAS (9.4) into xlsx files. Only xlsx files are possible because they are used then in another application.
However, when the files are exported and to be able to use them in another application, I must open and save them.
I've googled a little bit and if I understand correctly .xlsx files are compressed Zip files.
I wonder whether I can unzipped them within SAS code?
Thank you very much in advance!
Best regards,
Yuliya
Why do you open and save the xlsx files? Excel won't change anything by doing this.
I just checked this, and yes, Excel increases the filesize when saving to a new file. What's the use of a larger file when otherwise the content does not change?
@Kurt_Bremser wrote:
I just checked this, and yes, Excel increases the filesize when saving to a new file. What's the use of a larger file when otherwise the content does not change?
@Kurt_Bremser I'm too lazy to test/ check on this but I suspect the main difference is that Excel places a lot of MS style and junk that SAS didn't when creating the initial XLSX. I did do some tests about similar behavior of Word and RTF documents so that is what I'm basing this on.
And I agree that the OP has not describe a reason for the additional save step unless he meant he was doing something with individual tabs or such.
How are you exporting your files?
This used to be a common requirement if using ODS EXCEL or ODS HTML and piping to Excel as the file generated wasn't truly XLSX underneath but was XML or HTML.
In general, yes, you can unzip the file as desired. Typically I would recommend using a system command, which depends on your OS. Do you know if you have X Command enabled or do you need to use SAS commands only? What is your OS? Which zip program is available to you?
You can check if the option is set with the following:
proc options option = XCMD;
run;
This is an example of how to zip, a very similar approach can be used for unzip - just need to know the specifics.
options noxwait noxsync;
data _null_;
*Path to winzip program;
zipexe='"C:\Program Files\7-Zip\7z.exe" a -tzip';
*Zip file name;
zipfile="C:\My Documents\Sample.zip";
*File to be zipped;
file="C:\Temp\Sample.txt";
*Full cmd line to be passed to command line. It embeds the quotes for paths with spaces;
cmd = zipexe ||' "'|| zipfile ||'" "'|| file ||'"' ;
*Place note in log;
putlog "NOTE-Processing command" cmd;
*Execute command;
call system(cmd);
run;
SAS + 7 Zip to unzip
https://stackoverflow.com/questions/20434945/sas-unzipping-multiple-dat-files-at-once
Examples of SAS functions instead:
@Yuliya wrote:
Hi!
I do not use SAS very often and hope you can give me some tips.
The data must be exported from SAS (9.4) into xlsx files. Only xlsx files are possible because they are used then in another application.
However, when the files are exported and to be able to use them in another application, I must open and save them.
I've googled a little bit and if I understand correctly .xlsx files are compressed Zip files.
I wonder whether I can unzipped them within SAS code?
Thank you very much in advance!
Best regards,
Yuliya
Hi!
Thank you very much for the reply:
Here is the code that I use to export .xlsx file:
proc export data=data
outfile="data.xlsx"
dbms=xlsx label replace;
sheet='Data';
run;
Everything works ok.
When I get .xlsx file, it is not zipped in a general sence. What I mean is that it is compressed somehow, therefore before I can use it in another App, I have to open it and save again.
Do you have any ideas, what can be done?
Thank you very much in advance.
Regards,
Yuliya
What format is the "other app" expecting? SAS-created xlsx files are valid, I never had a problem opening them with LibreOffice or other non-Excel software.
Doesn't the app accept a file format that makes sense? I have yet to see software for tabular data that can't read CSV or an other character-separated or fixed-width file format.
Thank you. I will try 🙂
Have a nice weekend!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: