PROC EXPORT - Excel Size Out Of Proportion To SAS Dataset Size

JohnC — Fri, 08 Mar 2013 15:57:16 GMT

Hi,

I'm new to this community, so I hope I have the right forum for this question. I have a SAS file that I export to Excel on a daily basis. As you can see, it's really quite small -- never more than 100 observations at a time (file/field names blanked out or changed for confidentiality):

*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*

The CONTENTS Procedure

Data Set Name                                                         Observations           100
Member Type          DATA                                             Variables              8
Engine               V9                                               Indexes                0
Created              Friday, March 08, 2013 07:07:03 AM               Observation Length     67
Last Modified        Friday, March 08, 2013 07:07:03 AM               Deleted Observations   0
Protection                                                            Compressed             CHAR
Data Set Type                                                         Reuse Space            NO
Label                                                                 Point to Observations YES
Data Representation HP_UX_64, RS_6000_AIX_64, SOLARIS_64, HP_IA64    Sorted                 NO
Encoding             latin1 Western (ISO)

Engine/Host Dependent Information

Data Set Page Size          8192
Number of Data Set Pages    3
Number of Data Set Repairs 0
Filename
Release Created             9.0202M3
Host Created                AIX
Inode Number                1664019
Access Permission           rw-r-----
Owner Name
File Size (bytes)           32768

Alphabetic List of Variables and Attributes

# Variable Type Len Format Informat

1    fld_1               Char     10    $10.      $10.
2    fld_2               Num       8
3    fld_3               Num       8
4    fld_4               Num       8
5    fld_5               Num       8
6    fld_6               Num       8
7    fld_7               Num       8
8    fld_8               Char      9

*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*

I then perform the following:

proc export data=SmallFile
            outfile="SmallFile.xls"
            DBMS=xls
            label
            replace;
run;

NOTE: The export data set has 100 observations and 8 variables.
NOTE: "SmallFile.xls" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.17 seconds
cpu time 0.01 seconds

So far, so good. The Excel file created, however, is over 4MB in size. Any ideas as to why this is occurring? I've seen several posts talking about file sizes, but in everything I've seen the starting SAS dataset is very large, which is not the case here.

Thanks,

John

Re: PROC EXPORT - Excel Size Out Of Proportion To SAS Dataset Size

LarryWorley — Fri, 08 Mar 2013 17:40:20 GMT

Hi John,

I notice in the proc contents that your data set is compressed. How about checking the uncompressed size in sas and see if the size comparisons between sas and excel are comparable?

You can run proc contents on the data set created by the following code:

data notSoSmall (compress = no) ;

set smallFile;

run ;

Larry

Re: PROC EXPORT - Excel Size Out Of Proportion To SAS Dataset Size

JohnC — Fri, 08 Mar 2013 17:48:51 GMT

Worked like a charm, Larry -- thanks!

Re: PROC EXPORT - Excel Size Out Of Proportion To SAS Dataset Size

Reeza — Fri, 08 Mar 2013 18:18:52 GMT

If its 800 observations it shouldn't be 4MB.

Check your character variables are they $256. each or something?

Also try the other engines for export, e.g. dbms=excel and/or dbms=excelcs

topic Re: PROC EXPORT - Excel Size Out Of Proportion To SAS Dataset Size in SAS Programming

PROC EXPORT - Excel Size Out Of Proportion To SAS Dataset Size

Re: PROC EXPORT - Excel Size Out Of Proportion To SAS Dataset Size

Re: PROC EXPORT - Excel Size Out Of Proportion To SAS Dataset Size

Re: PROC EXPORT - Excel Size Out Of Proportion To SAS Dataset Size