topic Re: group in merge in SAS Procedures

group in merge

podarum — Fri, 25 Nov 2011 15:54:16 GMT

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

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

group in merge

art297 — Fri, 25 Nov 2011 16:02:36 GMT

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;

group in merge

Pritish — Fri, 25 Nov 2011 19:41:21 GMT

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;

group in merge

Ksharp — Sat, 26 Nov 2011 02:26:08 GMT

I am a little confused.

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

Ksharp

Re: group in merge

podarum — Sat, 26 Nov 2011 03:38:13 GMT

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

Re: group in merge

Tom — Sun, 27 Nov 2011 03:54:36 GMT

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;

Re: group in merge

podarum — Sun, 27 Nov 2011 05:40:52 GMT

Yes Price and City

Re: group in merge

art297 — Sun, 27 Nov 2011 16:14:21 GMT

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?

Re: group in merge

podarum — Sun, 27 Nov 2011 16:17:35 GMT

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

Re: group in merge

art297 — Sun, 27 Nov 2011 16:47:17 GMT

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;