topic Re: Join tables in SAS Procedures

Join tables

SP2 — Fri, 09 Oct 2009 19:08:28 GMT

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

Re: Join tables

Flip — Fri, 09 Oct 2009 19:14:54 GMT

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.

Re: Join tables

SP2 — Fri, 09 Oct 2009 19:57:21 GMT

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.

Re: Join tables

deleted_user — Fri, 09 Oct 2009 20:37:21 GMT

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.

Re: Join tables

SP2 — Fri, 09 Oct 2009 20:49:22 GMT

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.

Re: Join tables

deleted_user — Fri, 09 Oct 2009 21:01:33 GMT

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;

Re: Join tables

SP2 — Fri, 09 Oct 2009 21:16:48 GMT

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.