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

My data has multiple records per ID, where the date sometimes differs, and doesn't in other cases. 

 

data have;                    
   input ID DATE varx;     
   datalines;            
1 01JAN2020 A
1 03JAN2020 B
2 01MAR2020 A
2 01MAR2020 B ; [5]

I want to select the earliest date of an ID, unless they have the same date, where I then want to select the record with "B". I'm not sure how to switch between criteria to first look at date, but then use different criteria if the date is the same.

 

Desired output:

ID     DATE               varx

1     01JAN2020        A

2     01MAR2020       B

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20
data have;                    
   input ID DATE :date9. varx $;
   format DATE date9.;
   datalines;
1 01JAN2020 A
1 03JAN2020 B
2 01MAR2020 A
2 01MAR2020 B
;

proc sort data=have;
	by ID DATE descending varx;
run;

data want;
	set have_sorted;
	by ID DATE descending varx;
	if first.ID then output;
run;

View solution in original post

3 REPLIES 3
ed_sas_member
Meteorite | Level 14

Hi @MB_Analyst 

 

Here is an approach to achieve this:

data have;                    
   input ID DATE:date9. varx $;
   format DATE date9.;
   datalines;            
1 01JAN2020 A
1 03JAN2020 B
2 01MAR2020 A
2 01MAR2020 B
;

proc sort data=have out=have_sorted nodupkey;
	by ID DATE descending varx;
run;

data want;
	set have_sorted;
	by ID DATE descending varx;
	if first.ID then output;
run;
PeterClemmensen
Tourmaline | Level 20
data have;                    
   input ID DATE :date9. varx $;
   format DATE date9.;
   datalines;
1 01JAN2020 A
1 03JAN2020 B
2 01MAR2020 A
2 01MAR2020 B
;

proc sort data=have;
	by ID DATE descending varx;
run;

data want;
	set have_sorted;
	by ID DATE descending varx;
	if first.ID then output;
run;
novinosrin
Tourmaline | Level 20

Hi @MB_Analyst  Assuming your sample is a representative one and is already sorted in ascending Id Date var, 

data have;                    
   input ID DATE :date9. varx $1.;     
   format date date9.;
   datalines;            
1 01JAN2020 A
1 03JAN2020 B
2 01MAR2020 A
2 01MAR2020 B
; 
data want;
 do _n_=1 by 1 until(last.id);
  do until(last.date);
   set have;
   by id date;
  end;
  if _n_=1 then output;
 end;
run;
ID DATE varx
1 01JAN2020 A
2 01MAR2020 B

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 905 views
  • 0 likes
  • 4 in conversation