BookmarkSubscribeRSS Feed
riya275
Obsidian | Level 7

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

4 REPLIES 4
Ksharp
Super User

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
Obsidian | Level 7
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?
Kurt_Bremser
Super User

@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.

PGStats
Opal | Level 21

So it looks like this would be better:

 

data a6.s;
merge a6.smb a6.churn;
by product_id;
run;
PG

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 4596 views
  • 0 likes
  • 4 in conversation