## Data Logic Help

Occasional Contributor
Posts: 12

# Data Logic Help

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

PROC Star
Posts: 1,805

## Re: Data Logic Help

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

Super User
Posts: 10,778

## Re: Data Logic Help

``````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;``````
Frequent Contributor
Posts: 103

## Re: Data Logic Help

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

Super User
Posts: 10,778

## Re: Data Logic Help

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 ).

Discussion stats
• 4 replies
• 132 views
• 0 likes
• 4 in conversation