BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
John4
Obsidian | Level 7

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

3 REPLIES 3
Cynthia_sas
SAS Super FREQ

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

John4
Obsidian | Level 7

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

Reeza
Super User

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

 

 


 

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

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