Proc SQL left Join

[deleted_user]

Hi All...
I have 2 datasets with each 500 MB of size approx...when im left joining them the final dataset is coming around 7GB Can any one tell me the reason and how can i reduce that.Both of the input datasets are not sorted or donot have index. and i cant do a proc sort on them.and they contain duplicates values also...as they contain duplicate values i cant even create index on key column
Thanks & Regards
Sam

[Doyleuk]

Can you post your code so that we can view it

[Peter_C]

OK, having described the input side of your data model, it is understandable that proc sql might have some trouble. You can help proc sql to control that explosion of data with a variety of conditions in the join and in where clause(s). If these are unneccessary, you may have to accept that the output side of your problem will be a 7GB file.
When you offer code, it might become possible to help you more.

PeterC

[Doc_Duke]

Sam,

The problem is the rows with duplicates on the join fields. If you have, say, 4 rows in the left table that match 5 rows in the right table, you will have 20 rows in the result table.

It's still a left join (all the rows in the left table are kept), but it generates a mini-Cartesian product for the matches.

Doc Muhlbaier
Duke