BookmarkSubscribeRSS Feed
podarum
Quartz | Level 8

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 

9 REPLIES 9
art297
Opal | Level 21

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
Quartz | Level 8

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
Super User

I am a little confused.

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

Ksharp

podarum
Quartz | Level 8

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

Tom
Super User Tom
Super User

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
Quartz | Level 8

Yes Price and City

art297
Opal | Level 21

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
Quartz | Level 8

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

art297
Opal | Level 21

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;

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 923 views
  • 0 likes
  • 5 in conversation