BookmarkSubscribeRSS Feed
Yuliya
Obsidian | Level 7

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

 

   

 

 

11 REPLIES 11
Yuliya
Obsidian | Level 7
Hi!

Thank you very much for a quick reply.

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

Have a nice day 🙂
Kurt_Bremser
Super User

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
Super User

@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
Super User

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7
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
Super User

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
Super User
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
Obsidian | Level 7

Thank you. I will try 🙂

 

Have a nice weekend!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 11 replies
  • 774 views
  • 3 likes
  • 4 in conversation