DATA Step, Macro, Functions and more

Proc sql left join

Reply
Contributor
Posts: 23

Proc sql left join

[ Edited ]

I have to use proc sql to left join two tables

proc sql;
create table a6.s as
select f.*, c.*
from a6.smb as f 
left join
a6.churn as c 
on f.PRODUCT_ID=c.PRODUCT_ID;
quit;

Table a6.smb has 99999 rows (and 300 columns) and a6.churn has 6465 rows (and 2 columns) . I want to add the column from churn table to smb table where product_id is the common column in the two tables.

when i use this code i get 12930 rows (6465 *2=12930) and 301 columns .

But isnt left join supposed to give me 99999 rows and that's what I want.

What is wrong with my code?

also i get the warning that Product_id already exists in a6.s

Super User
Posts: 10,860

Re: Proc sql left join

Because your key variable PRODUCT_ID has duplicated value in table a6.smb.

 

and both table have the same variable PRODUCT_ID, therefore you get the WARNING.

Contributor
Posts: 23

Re: Proc sql left join

Each product_id is repeated thrice in a6.smb. but its 9 times in the final a6.s table. Why is it there 9 times?
Super User
Posts: 10,623

Re: Proc sql left join


riya275 wrote:
Each product_id is repeated thrice in a6.smb. but its 9 times in the final a6.s table. Why is it there 9 times?

Because it's also repeated thrice in churn.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Esteemed Advisor
Posts: 5,627

Re: Proc sql left join

So it looks like this would be better:

 

data a6.s;
merge a6.smb a6.churn;
by product_id;
run;
PG
Ask a Question
Discussion stats
  • 4 replies
  • 209 views
  • 0 likes
  • 4 in conversation