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.
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;
end;
run;
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.