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
create table CD_Data
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;
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.
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.