Export to .xlsx file and unzipp them

[Yuliya]

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

[Kurt_Bremser]

Why do you open and save the xlsx files? Excel won't change anything by doing this.

[Yuliya]

Hi!

Thank you very much for a quick reply.

Actually, it does change: the size of the file.

Have a nice day 🙂

[Kurt_Bremser]

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?

[ballardw]

---

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

[Reeza]

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:

https://blogs.sas.com/content/sasdummy/2015/05/11/using-filename-zip-to-unzip-and-read-data-files-in...

---

@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

---

[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

[Kurt_Bremser]

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.

[Yuliya]

It actually expects .xlsx format.
A mistake that we get in another App when we try to import .xlsx file without first opening it is: that A2 has incorrect format. But it does have the right format.
When we open .xlsx file, we do not do anything there, just save again. However, the size of the file changes, it becomes bigger and then it is imported into App without any mistakes.

[Kurt_Bremser]

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.

[Reeza]

Could you add a VB script that converted it after the fact and call that from SAS?
This could be modified to do that:
https://gist.github.com/statgeek/878e585102c14e01581f55dbe972d27e

Try changing this line:
mybook.SaveAs ""&default.\"" & Filename & "".csv"", 6";

to:
mybook.SaveAs ""&default.\"" & Filename & "".xlsx"", 51";

[Yuliya]

Thank you. I will try 🙂

Have a nice weekend!