DATA Step, Macro, Functions and more

Summing Observations using range of dates that varies by observation

Reply
New Contributor s2d
New Contributor
Posts: 2

Summing Observations using range of dates that varies by observation

Greetings:

I have a dataset like the following:

DateDaysVar1  .....Var10
1/1/2011105
1/2/2011158
1/3/2011123
1/4/2011369
1/5/2011593
1/6/20116112
1/7/20114122
1/8/2011796

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:

DateDaysVar1 ......Var10Cycle_Sum_Var1....Cycle_Sum_Var10
1/1/2011105
1/2/2011158
1/3/2011123
1/4/20113693320
1/5/20115935228
1/6/201161126330
1/7/201141223816
1/8/201179674

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.

PROC Star
Posts: 7,363

Summing Observations using range of dates that varies by observation

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 lastSmiley Happy;

  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;

New Contributor s2d
New Contributor
Posts: 2

Summing Observations using range of dates that varies by observation

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!

Ask a Question
Discussion stats
  • 2 replies
  • 136 views
  • 3 likes
  • 2 in conversation