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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.