@inid
Nothing to be sorry about. Below code should return what you're after.
The yrdif() function as I've used it calculates the AGE between two dates. As I understand you this should be something between 0 and exactly 1 (if the end date is one day later yrdif() would return a value of 1 with some decimals).
data have;
infile datalines truncover dlm=' ' dsd;
input ID Observation_date:anydtdte. Status;
format Observation_date date9.;
datalines;
1 31/12/2016 0
1 31/01/2017 0
1 31/12/2017 1
1 31/01/2018 1
1 31/03/2018 2
1 31/01/2020 3
;
run;
proc sql;
select distinct
l.*,
max(r.status) as max_status_within_year
from
have l
left join
have r
on
l.id=r.id
and yrdif(l.Observation_date,r.Observation_date) between 0 and 1
/* and yrdif(intnx('month',l.Observation_date,0,'b'),intnx('month',r.Observation_date,0,'b')) between 0 and 1*/
/* and intck('month',l.Observation_date,r.Observation_date) between 0 and 12*/
group by l.id,l.Observation_date
having max(r.status)=r.status
;
quit;
The first alternative condition in comments first aligns the two dates to the beginning of the month and only then calculates the AGE.
The 2nd alternative condition calculates the number of moths boundaries between two dates (31Jan2017, 01Feb2017 would return 1).
... View more
