topic Re: Complement of a data set by keeping the most recent date in SAS Procedures

Complement of a data set by keeping the most recent date

John4 — Sat, 15 Apr 2017 14:55:59 GMT

I have two data A and B with more than 500 observations like this :

data A :

ID NAME DATE

320 ANNA 12NOV2016

177 JOSH 14FEB2017

177 JOSH 16JAN2015

185 MIKE 03APR2017

185 MIKE 12FEB2017

190 ANGIE 12JAN2017

data B :

ID NAME

320 ANNA

177 JOSH

160 MAX

I want to find observations which are in A but are not in B and by keeping the most recent date. I mean how can I get something like this ? :

ID NAME DATE

185 MIKE 03APR2017

190 ANGIE 12JAN2017

Re: Complement of a data set by keeping the most recent date

Cynthia_sas — Sat, 15 Apr 2017 14:20:38 GMT

Hi:

Just curious, how do you define "most recent". I see 2 dates for Mike -- one in April and one in Feb. I would consider the one in April to be "most recent", but in your desired output, you show the Feb date as the one you want. Can you clarify whether you want the earliest date (Feb) or the most recent date (April)?

cynthia

Re: Complement of a data set by keeping the most recent date

Reeza — Sat, 15 Apr 2017 14:32:39 GMT

Your previous question was also about most recent date. The solution there is similar here, first get the most recent and then filter your dataset, either via a LEFT SQL join or a MERGE, see below.

Assuming your data is sorted by name:

data want;

set name_dates names (In= InNames);

By Name;

if InNames;

run;

@John4 wrote:

I have two data A and B with more than 500 observations like this :

data A :

ID      NAME     DATE

320   ANNA     12NOV2016

177   JOSH      14FEB2017

177   JOSH     16JAN2015

185   MIKE      03APR2017

185   MIKE       12FEB2017

190   ANGIE    12JAN2017

data B :

ID       NAME

320    ANNA

177    JOSH

160    MAX

I want to find observations which are in A but are not in B and by keeping the most recent date. I mean how can I get something like this ? :

ID      NAME     DATE

185 MIKE     12FEB2017

190   ANGIE    12JAN2017

Re: Complement of a data set by keeping the most recent date

John4 — Sat, 15 Apr 2017 14:57:44 GMT

You're right. It was a typo, I edit the topic. I mean, MIKE <---> 03APR2017