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
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.
@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.
So it looks like this would be better:
data a6.s;
merge a6.smb a6.churn;
by product_id;
run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: