topic SAS proc export to reduce excel file size in SAS Programming

SAS proc export to reduce excel file size

phoenix31 — Thu, 18 Feb 2021 00:57:26 GMT

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.

Re: SAS proc export to reduce excel file size

qoit — Thu, 18 Feb 2021 02:22:36 GMT

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.

Re: SAS proc export to reduce excel file size

andreas_lds — Thu, 18 Feb 2021 05:48:03 GMT

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?

Re: SAS proc export to reduce excel file size

phoenix31 — Thu, 18 Feb 2021 15:34:24 GMT

Thank you for the information..

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

Re: SAS proc export to reduce excel file size

Kurt_Bremser — Thu, 18 Feb 2021 15:44:27 GMT

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.