topic Re: Data Logic Help in SAS Programming

Data Logic Help

Almoha — Tue, 08 Aug 2017 19:29:03 GMT

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

Re: Data Logic Help

novinosrin — Tue, 08 Aug 2017 19:47:48 GMT

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

Re: Data Logic Help

Ksharp — Wed, 09 Aug 2017 13:41:55 GMT

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;

Re: Data Logic Help

MarkWik — Wed, 09 Aug 2017 16:54:26 GMT

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

Re: Data Logic Help

Ksharp — Thu, 10 Aug 2017 12:58:44 GMT

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