DATA Step, Macro, Functions and more

Proc SQL left Join

Reply
N/A
Posts: 0

Proc SQL left Join

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
Occasional Contributor
Posts: 12

Re: Proc SQL left Join

Posted in reply to deleted_user
Can you post your code so that we can view it
Valued Guide
Posts: 2,177

Re: Proc SQL left Join

Posted in reply to deleted_user
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
Trusted Advisor
Posts: 2,116

Re: Proc SQL left Join

Posted in reply to deleted_user
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
Ask a Question
Discussion stats
  • 3 replies
  • 1028 views
  • 0 likes
  • 4 in conversation