DATA Step, Macro, Functions and more

Data Logic Help

Reply
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: 283

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,035

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: 75

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,035

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

Ask a Question
Discussion stats
  • 4 replies
  • 104 views
  • 0 likes
  • 4 in conversation