group in merge

[podarum]

I need to group similar observations in a merge step.  This is what I mean:

HAVE_1

Price     City     Name    

12.55  Toronto    25KN

12.55  Toronto    56HW

12.55  Toronto    93KT

14.83  Calgary    32BD

12.63  Ottawa     23OP

18,94  Montreal   34RP

HAVE_2

Price     City     Name

12.55  Toronto    66LG

14.83  Calgary    26TR

12,63  Ottawa     38KG

I merge by City only.

And get this:

12.55 Toronto

12.55 Toronto

12.55 Toronto

14.83  Calgary   

12.63  Ottawa    

18,94  Montreal  

and want this

WANT

12.55 Toronto

14.83  Calgary   

12.63  Ottawa    

18,94  Montreal 

[art297]

I probably don't correctly understand what you are asking.  From your description I would simply (1) concatenate the two files:

data want;

    set have_1 (keep=price city) have_2 (keep=price city);

run;

and then (2) use proc sort to get rid of the duplicates:

proc sort data=want nodupkey;

   by city;

run;

[Pritish]

If "Name" col is not critical then you also use the following code to get the desired results:

proc sql;

create table want as

select distinct have_1.price, have_1.city

from have_1 left outer join have_2

on have_1.city = have_2.city;

quit;

[Ksharp]

I am a little confused.

The price variable in WANT table is from HAVE_1 or from HAVE_2?

Ksharp

[podarum]

It doesn't matter because it is the same in both tables.

[Tom]

Sounds like you want to do a set with a BY statement.

The examples are not in the CITY order so I am not sure what your BY variables are.  Do you want to include PRICE in the sort order?

data want;

   set have1 have2;

   by price city;

   if first.city;

   keep price city;

run;

[podarum]

Yes Price and City

[art297]

If there were two or more entries for a city, with different prices, would you want one or more than one record to result for that city?

[podarum]

In that case then 2 separate entries, if for example there was a 12.85  Toronto , then that woulod be a separat entry

[art297]

In that case, either Tom's suggested code should work (if you first sort the data by price and city), or a slightly modified version of the code I offered:

data want;

    set have_1 (keep=price city) have_2 (keep=price city);

run;

and then (2) use proc sort to get rid of the duplicates:

proc sort data=want nodupkey;

   by price city;

run;