SAS proc export to reduce excel file size

[phoenix31]

Hi Team,

I use the below code to export a dataset into an xlsx file which is 24Mb in size.

proc export data= work.mydata
Outfile = "/location/file.xlsx" dbms = xlsx replace;
sheet='Base';
run;

I want to reduce the size of the file as this file is anticipated to grow in size.

I tried generating a .csv file in many ways but they all increased the file size to 55 Mb!

CSV code1 used:

/****file size 55Mb******/

filename _dataout zip "/location/file.zip" member="file.csv";

proc export data=work.mydata
outfile=_dataout
dbms=csv replace;
run;

/* although the zip file compressed the file but when tried to save the .csv on the desktop it retained the original csv size of 55Mb*/

csv code2 used:

proc export data=work.mydata
outfile="location/file.csv" dbms = csv replace;
run;

/****file size 55Mb******/

Csv code3 used:

data _null_;
set work.mydata;
file '/location/file.csv' dlm=',' dsd ;
put (_all_) (:) ;
run;

/****file size 55Mb******/

can anyone please help me reduce the file size.

[qoit]

Couple of things that might work:

1. Cutting down the CHAR variable lengths.

2. Remove compression from the SAS dataset if compressed. Use PROC CONTENTS to check.

[andreas_lds]

xlsx-files will be smaller than csv-files most times, because a xslx-file is just a zipped collection of xml-files. On a windows system, i would recommend using file-system-compression, but i don't know if a similar feature is available in your environment. Why do you have to export the file at all? Why not saving the dataset in a permanent library?

[phoenix31]

Thank you for the information..

The file has to be sent through email and the team needs it in one of the excel formats..

[Kurt_Bremser]

As noted, Excel files are already ZIP-compressed, and you could only make your file smaller by reducing the data as such. And I don't mean to get rid of blanks (these are eliminated by the compression anyway), you have to reduce information.

Instead of sending around emails, use a shared storage resource and send links to that.

We simply store such data on our SAS server, and have users use SFTP clients to download it.