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

[JohnC]

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

[LarryWorley · Accepted solution]

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

[JohnC]

Worked like a charm, Larry -- thanks!

[Reeza]

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