topic Summing Observations using range of dates that varies by observation in SAS Programming

Summing Observations using range of dates that varies by observation

s2d — Thu, 01 Dec 2011 19:31:58 GMT

Greetings:

I have a dataset like the following:

Date	Days	Var1 .....	Var10
1/1/2011		10	5
1/2/2011		15	8
1/3/2011		12	3
1/4/2011	3	6	9
1/5/2011	5	9	3
1/6/2011	6	11	2
1/7/2011	4	12	2
1/8/2011	7	9	6

I would like to sum the values of each variable in the array Var1 - Var 10 using the number of days in the cycle (variable is named 'Days'). Such that I could produce the following dataset as output:

Date	Days	Var1 ......	Var10	Cycle_Sum_Var1....	Cycle_Sum_Var10
1/1/2011		10	5
1/2/2011		15	8
1/3/2011		12	3
1/4/2011	3	6	9	33	20
1/5/2011	5	9	3	52	28
1/6/2011	6	11	2	63	30
1/7/2011	4	12	2	38	16
1/8/2011	7	9	6	74	33

Your suggestions are much appreciated! I know proc means, basic arrays and some do loops, etc. I'm not sure if a Macro or Proc SQL is called for in this situation.

Summing Observations using range of dates that varies by observation

art297 — Thu, 01 Dec 2011 20:39:30 GMT

If you aren't missing any dates and you will never need to accumulate beyond 10 days, then you could use something like:

data have;

informat date mmddyy10.;

format date mmddyy10.;

input Date Days Var1-Var10;

cards;

1/1/2011 . 10 1 1 1 1 1 1 1 1 5

1/2/2011 . 15 1 1 1 1 1 1 1 1 8

1/3/2011 . 12 1 1 1 1 1 1 1 1 3

1/4/2011 3 6 1 1 1 1 1 1 1 1 9

1/5/2011 5 9 1 1 1 1 1 1 1 1 3

1/6/2011 6 11 1 1 1 1 1 1 1 1 2

1/7/2011 4 12 1 1 1 1 1 1 1 1 2

1/8/2011 7 9 1 1 1 1 1 1 1 1 6

;

data want (drop=i j last:);

set have;

array current(10) var1-var10;

array last(10,10);

array cycle_sum_var(10);

do i=1 to 10;

last(i,1)=lag1(current(i));

last(i,2)=lag2(current(i));

last(i,3)=lag3(current(i));

last(i,4)=lag4(current(i));

last(i,5)=lag5(current(i));

last(i,6)=lag6(current(i));

last(i,7)=lag7(current(i));

last(i,8)=lag8(current(i));

last(i,9)=lag9(current(i));

last(i,10)=lag10(current(i));

end;

if not missing(days) then do i=1 to 10;

cycle_sum_var(i)=current(i);

do j=1 to days-1;

cycle_sum_var(i)=sum(cycle_sum_var(i),last(i,j));

end;

run;

Summing Observations using range of dates that varies by observation

s2d — Thu, 01 Dec 2011 21:36:44 GMT

Thanks very much, art. 'Day's goes up to 30 in my dataset, so I made the necessary adjustments and it works quite well. Many thanks!