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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.