Doing sum of n rows before after a point

[PhuongNguyen]

Hi there,

Can you have a look?

I have the data set

data WORK.CAR;
infile datalines dsd truncover;
input RIC:$6. Date:DATE11. edate:DATE11. beta:32. ar:32.;
format Date DATE11. edate DATE11.;
datalines;
AAM.HM 09-JAN-2013 . 0.0825120191 0.0268880943
AAM.HM 10-JAN-2013 . 0.0825120191 -0.014652488
AAM.HM 11-JAN-2013 . 0.0825120191 0.0124439573
AAM.HM 14-JAN-2013 . 0.0825120191 -0.032734738
AAM.HM 16-JAN-2013 . 0.0825120191 0.0591856527
AAM.HM 17-JAN-2013 17-JAN-2013 0.0825120191 -0.060624823
AAM.HM 18-JAN-2013 . 0.0825120191 0.0116597411
AAM.HM 21-JAN-2013 . 0.0825120191 0.0032585894
AAM.HM 22-JAN-2013 . 0.0825120191 0.0079327087
AAM.HM 23-JAN-2013 . 0.0825120191 0.0029502964
AAM.HM 24-JAN-2013 . 0.0825120191 -0.010330232
AAM.HM 25-JAN-2013 . 0.0825120191 0.0092494525
AAM.HM 28-JAN-2013 28-JAN-2013 0.0825120191 -0.001155795
AAM.HM 29-JAN-2013 . 0.0825120191 -0.01729687
AAM.HM 30-JAN-2013 . 0.0825120191 -0.023300046
AAM.HM 31-JAN-2013 . 0.0825120191 -0.000380874
AAM.HM 01-FEB-2013 . 0.0825120191 0.0180702643
AAM.HM 04-FEB-2013 . 0.0825120191 -0.013536172
AAM.HM 09-APR-2013 . 0.0825120191 -0.043363278
AAM.HM 10-APR-2013 . 0.0825120191 0.0397868991
;;;;

Whenever EDATE takes a value, I want to take sum of 5 rows before that value and also another sum of 5 rows after that value with variable AR. My data have different RICs. EDATEs sometimes appear within 5 rows from each other. Thank you for your help.

[Kurt_Bremser · Accepted solution]

Use a temporary array to store 5 values, and run the process once straight and once reverse:

data WORK.CAR;
infile datalines truncover;
input RIC:$6. Date :dATE11. edate :dATE11. beta:32. ar:32.;
format Date DATE11. edate DATE11.;
datalines4;
AAM.HM 09-JAN-2013 . 0.0825120191 0.0268880943
AAM.HM 10-JAN-2013 . 0.0825120191 -0.014652488
AAM.HM 11-JAN-2013 . 0.0825120191 0.0124439573
AAM.HM 14-JAN-2013 . 0.0825120191 -0.032734738
AAM.HM 16-JAN-2013 . 0.0825120191 0.0591856527
AAM.HM 17-JAN-2013 17-JAN-2013 0.0825120191 -0.060624823
AAM.HM 18-JAN-2013 . 0.0825120191 0.0116597411
AAM.HM 21-JAN-2013 . 0.0825120191 0.0032585894
AAM.HM 22-JAN-2013 . 0.0825120191 0.0079327087
AAM.HM 23-JAN-2013 . 0.0825120191 0.0029502964
AAM.HM 24-JAN-2013 . 0.0825120191 -0.010330232
AAM.HM 25-JAN-2013 . 0.0825120191 0.0092494525
AAM.HM 28-JAN-2013 28-JAN-2013 0.0825120191 -0.001155795
AAM.HM 29-JAN-2013 . 0.0825120191 -0.01729687
AAM.HM 30-JAN-2013 . 0.0825120191 -0.023300046
AAM.HM 31-JAN-2013 . 0.0825120191 -0.000380874
AAM.HM 01-FEB-2013 . 0.0825120191 0.0180702643
AAM.HM 04-FEB-2013 . 0.0825120191 -0.013536172
AAM.HM 09-APR-2013 . 0.0825120191 -0.043363278
AAM.HM 10-APR-2013 . 0.0825120191 0.0397868991
;;;;
run;

data int1;
set car;
by ric;
count = _n_;
array ar_ar {5} _temporary_;
if first.ric then do i = 1 to 5;
  ar_ar{i} = 0;
end;
if edate then sum_ar_before = sum(of ar_ar{*});
ar_ar{mod(count,5)+1} = ar;
drop i;
run;

proc sort data=int1;
by descending count;
run;

data int2;
set int1;
by ric;
array ar_ar {5} _temporary_;
if first.ric then do i = 1 to 5;
  ar_ar{i} = 0;
end;
if edate then sum_ar_after = sum(of ar_ar{*});
ar_ar{mod(count,5)+1} = ar;
drop i;
run;

proc sort
  data=int2
  out=want (drop=count)
;
by count;
run;

[PhuongNguyen]

Hi @Kurt_Bremser,

Thank you for your codes. I have tried the codes.

int1 works well. Yet, when we reverse int1, the RICs are also reversed. Then int2 doesn't work since the variables are not properly sorted. My data have many different RICs. And EDATE sometimes appear within 5 rows from each other. I mean if we properly eliminate a duplicate sum with the current codes. Any idea? Thank you.

[Kurt_Bremser]

The step for int2 needs a descending option:

by descending ric;

My code gets you the sums of previous 5 / next 5. If you have overlaps, you need to define a logical rule for how to handle them. That logic can then be put into code.

[PhuongNguyen]

Thanks @Kurt_Bremser