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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.