Join tables

[SP2]

I have 2 data sets like these.
Data set 1
Product, Seg, ID, Loc_cd,
A1, SER, 200, 1
A2, OFF, 100, 1
A2, OFF, 100, 2
A2, OFF, 300, 1
A1, OFF, 101, 1
A1, OFF, 102, 2
A2, OFF, 101, 2

Dataset Price
Product, Seg, Price
A1, SER, 20
A1, OFF, 30
A2, SER, 40
A2, OFF, 50
What I need to do is to add another column in Dataset 1 named "Price".
Which basically look Product and Sgments. When I do Left Join, It is taking cartician product including Loc_cd.The uniqueness of data in dataset 1 is Columns Product,Seg and Loc_cd.Is there any way I can solve this?
Thanks

[Flip]

Probably. What is the unique key for the second set? If you showed us your code we might be able to tell you how to better handle this.

[SP2]

Here is the code.
CREATE table Impact_1 as

SELECT
a.Product,
a.Seg,
a.Class,
a.ID,
a.Loc_nbr,
b.Price
FROM Dataset1 a
LEFT JOIN DatasetPrice b
ON a.Product=b.Product
AND a.Seg=b.Seg;
QUIT
;
The unique key in second set is Product and Seg.
Basically for the same ID has multiple locations with same product and Seg.
Thanks.

[deleted_user]

What do you want your final table or output to look like? Is it this?


product seg id loc_cd price

A1 OFF 101 1 30
A1 OFF 102 2 30
A1 SER 200 1 20
A2 OFF 101 2 50
A2 OFF 100 2 50
A2 OFF 100 1 50
A2 OFF 300 1 50

Thanks.

[SP2]

Yes,
But instead of giving me 7 rows in the final table it is giving little over than this.
In my original data there were 3058 rows for table 1,3817 for second set, but final set is giving me 3548.

[deleted_user]

Here is the code I used to create that output.. In your code example you have a column called 'class' that I don't see in your orginal tables. Could that be causing your extra records?

data dataset1;
infile datalines delimiter = ',';
input product $ seg $ id $ loc_cd ;
datalines ;
A1,SER,200,1
A2,OFF,100,1
A2,OFF,100,2
A2,OFF,300,1
A1,OFF,101,1
A1,OFF,102,2
A2,OFF,101,2
;
run;

data price;
infile datalines delimiter = ',';
input product $ seg $ price ;
datalines ;
A1,SER,20
A1,OFF,30
A2,SER,40
A2,OFF,50
;
run;
proc sql;
CREATE table Impact_1 as

SELECT
a.Product,
a.Seg,
a.ID,
a.Loc_cd,
b.Price
FROM Dataset1 a
LEFT JOIN Price b
ON a.Product=b.Product
AND a.Seg=b.Seg;
QUIT;

[SP2]

It did work.Thank you. About the Class, that is in my original table. I was simplifying the table for this forum.
Thank you very much.