topic Re: Datatable size issue in SAS Procedures

Datatable size issue

deleted_user — Fri, 10 Apr 2009 20:27:23 GMT

I started out doing my work against a 5mb data table. The data table has 136 columns and around 13,000 rows.

I start off by pulling out my primary key columns(projectid, lineid, personid) and 3 data columns plus I added 3 columns that hold the length of each field in the 3 data columns. My new data table is now 9mb. Then I do all my cleaning and i'm at 12mb. When I try the code below to add my 7 new columns with the original table into a new table it is 620mb.

When I do this

proc sql;
create table CD_Data
as
Select OD.*, SSN.BM, SSN.BD, SSN.BY, SSN.DM, SSN.DD, SSN.DY, SSN.SSN
From OD_Data OD inner join SSN_DATA SSN on OD.projectid = SSN.projectid and OD.lineid = SSN.lineid and OD.personid = SSN.personid;
quit;

I'm not sure how adding 7 text columns will be that much space? 6 of those columns are completely filled. So I run a seperate data step clearing out the default values so they are blank and the data file was still around 600mb.

I've never looked at this aspect of sas so I would appreciate any help.

Thank You

Re: Datatable size issue

sbb — Fri, 10 Apr 2009 20:31:36 GMT

Analyze your SAS output data library / member with PROC CONTENTS to verify the SAS variables captured, their type and lengths and the overall size and content of your SAS database.

Scott Barry
SBBWorks, Inc.

Re: Datatable size issue

deleted_user — Fri, 10 Apr 2009 20:53:25 GMT

Scott,
Thanks for the answer that is exactly what my issue was. I was not compressing the data so I creating another data set after my proc sql. This time is put data test (Compress=CHAR) that is what the original table had as it's compression. I ran that and now it's around 6mb.

Thank You
Jerry