turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Join tables

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-09-2009 03:08 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-09-2009 03:14 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-09-2009 03:57 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-09-2009 04:37 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-09-2009 04:49 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-09-2009 05:01 PM

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;

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-09-2009 05:16 PM

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.

Thank you very much.