topic Re: Select Earliest date if different, otherwise use different criteria in SAS Programming

Select Earliest date if different, otherwise use different criteria

MB_Analyst — Wed, 18 Mar 2020 17:34:42 GMT

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

Re: Select Earliest date if different, otherwise use different criteria

ed_sas_member — Wed, 18 Mar 2020 17:47:22 GMT

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;

Re: Select Earliest date if different, otherwise use different criteria

PeterClemmensen — Wed, 18 Mar 2020 17:48:40 GMT

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;

Re: Select Earliest date if different, otherwise use different criteria

novinosrin — Wed, 18 Mar 2020 18:04:00 GMT

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