apolozies for this data please try this code
data test;
length id 8 value 8 have 8;
infile datalines4 dlm=',' missover dsd;
input id date_of_value :ddmmyy10. value have ;
format date_of_value date9.;
datalines4;
109999999,05/12/2017,42376,1
109999999,05/12/2017,42376,2
109999999,13/12/2017,42521,1
109999999,13/12/2017,42521,2
109999999,19/03/2018,43395,1
109999999,19/03/2018,43395,2
109999999,19/03/2018,43395,3
109999999,19/03/2018,43395,4
109999999,04/06/2018,43953,1
109999999,04/06/2018,43953,2
109999999,17/09/2018,44899,1
109999999,17/09/2018,44899,2
109999998,21/04/2018,4788,1
109999998,21/04/2018,4788,2
109999998,01/06/2018,4788,3
109999998,01/06/2018,4788,4
109999998,23/09/2018,4718,1
109999998,23/09/2018,4718,2
109999997,09/07/2018,8717,1
109999997,09/07/2018,8717,2
109999997,01/09/2018,8750,1
109999997,01/09/2018,8750,2
109999995,15/02/2018,22868,1
109999995,15/02/2018,22868,2
109999995,07/09/2018,22631,1
109999995,07/09/2018,22631,2
109554995,05/12/2017,92376,1
109554995,05/12/2017,92376,2
109554995,13/12/2017,92521,1
109554995,13/12/2017,92521,2
109554995,19/03/2018,93395,1
109554995,19/03/2018,93395,2
109554995,19/03/2018,93395,3
109554995,19/03/2018,93395,4
109554995,04/06/2018,93953,1
109554995,04/06/2018,93953,2
109554995,11/07/2018,94953,1
;;;;
run;
proc sort data=test;
by id descending DATE_OF_VALUE have;
run;
data want;
set test;
by id descending DATE_OF_VALUE have;
if first.id then _want=.;
if first.DATE_OF_VALUE then _want+1;
if first.DATE_OF_VALUE and _want ne . then want=_want;
drop _want;
run;
proc sort data=want;
by id DATE_OF_VALUE ;
run;
... View more
