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
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;
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;
I am a little confused.
The price variable in WANT table is from HAVE_1 or from HAVE_2?
Ksharp
It doesn't matter because it is the same in both tables.
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;
Yes Price and City
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?
In that case then 2 separate entries, if for example there was a 12.85 Toronto , then that woulod be a separat entry
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.