Help using Base SAS procedures

Complement of a data set by keeping the most recent date

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

Complement of a data set by keeping the most recent date

[ Edited ]

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

 

 


Accepted Solutions
Solution
‎04-15-2017 10:58 AM
Grand Advisor
Posts: 16,926

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

[ Edited ]

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

 

 


 

View solution in original post


All Replies
SAS Super FREQ
Posts: 8,685

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

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

Contributor
Posts: 33

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

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

Solution
‎04-15-2017 10:58 AM
Grand Advisor
Posts: 16,926

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

[ Edited ]

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

 

 


 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 119 views
  • 2 likes
  • 3 in conversation