Data Logic Help

[Almoha]

Hi All

data have ;
input id date1 res date2;
cards;
111 10AUG2017 10 10AUG2017
111 10SEP2017 20 10SEP2017
111 11SEP2017 30 11SEP2017
111 09MAR2018 18 09MAR2018
;
run;

i am trying to calculate  the mean of res that 6 months(180 days)  prior to each date1 from date2

ID      date1        res   date2           mean
111 10AUG2017 10   10AUG2017     .
111 10SEP2017 20    10SEP2017    10
111 11SEP2017 30    11SEP2017     15
111 09MAR2018 18   09MAR2018    25

[novinosrin]

data have ;

input id date1 : date9. res date2 : date9.;

format date1 date2 date9.;

cards;

111 10AUG2017 10 10AUG2017

111 10SEP2017 20 10SEP2017

111 11SEP2017 30 11SEP2017

111 09MAR2018 18 09MAR2018

;

run;

data want;

do _n_=1 by 1 until(last.id);

set have;

by id;

k=lag1(res);

k2=lag2(res);

if _n_=1 then do;

output;

continue;

end;

else if _n_=2 then mean=k;

else mean=sum(k,k2)/2;

output;

end;

drop k:;

run;

Regards,

Naveen Srinivasan

[Ksharp]

data have ;
input id date1 : date9. res date2 : date9.;
format date1 date2 date9.;
cards;
111 10AUG2017 10 10AUG2017
111 10SEP2017 20 10SEP2017
111 11SEP2017 30 11SEP2017
111 09MAR2018 18 09MAR2018
;
run;
proc sql;
select *,(select mean(res) from have where id=a.id and
   intnx('month',a.date1,-6,'s') < date1< a.date1 )
 from have as a ;
quit;

[MarkWik]

Hi @Ksharp Can you please explain the compund expression:

 intnx('month',a.date1,-6,'s') < date1< a.date1 

I am keen to understand how that works with three operands. Thank you!

Mark 

[Ksharp]

pick up the data from HAVE 

where  date1 less than the current record's date1(a.date1)

    and date1 greater than ( the current record's date1 - 6 month ).