Transpose, mirror/invert, calculation by row

[ayin]

/* data have */
Period   Time_0 Time_1 Time_2 Time_3
Period_1   0       4      7      9
Period_2   1       5      8      .
Period_3   2       6      .      .
Period_4   3       .      .      .

/* transpose first */
Time     Period_1 Period_2 Period_3 Period_4
Time_0      0        1        2       3
Time_1      4        5        6       .
Time_2      7        8        .       .
Time_3      9        .        .       .

/* then mirror */
Time     Period_4 Period_3 Period_2 Period_1
Time_0      3        2        1       0
Time_1      .        6        5       4
Time_2      .        .        8       7
Time_3      .        .        .       9


/* lastly calculate */
if CountNum = 1:
Time Result
Time_1 3       /* 6/2 */
Time_2 1.6    /* 8/5 */
Time_3 1.29  /* 9/7 */

if CountNum = 2:
Time     Result
Time_1      3.67      /*  (5+6)/(1+2) */
Time_2      1.67     /* (7+8)/(4+5)  */
Time_3      1.29    /* 9/7          */

if CountNum = 3:
Time Result
Time_1 5       /* (4+5+6)/(0+1+2) */
Time_2 1.67   /* (7+8)/(4+5) */
Time_3 1.29  /* 9/7 */

Basically sum up the same number (&CountNum) of numbers, and then it is divided by the sum of numbers from the same columns but one row above.

Thanks.

[Reeza]

Can you explain the rules more clearly, what's the logic to start off with?

[ayin]

Have edited the post. Hopefully the new dataset would be able to demonstrate what has been done in each process.

[Astounding]

Depending on the size of your data set, you may be able to fit it all into a giant two-dimensional array.  That might let you go straight to the final calculations.  However, I agree with Reeza.  The rules for that final step are very unclear.

[ballardw]

And the purpose of this set is what exactly:

/* then mirror */
Time     Period_4 Period_3 Period_2 Period_1
Time_0      1        1        1       1
Time_1      .        2        2       2
Time_2      .        .        3       3
Time_3      .        .        .       4

Anything I can calculate from this in SAS I can calculate from the previous data set.

Are you replicating some process developed from Excel???? Generally not the best model for manipulation in SAS.

[Astounding · Accepted solution]

Based on the new explanation, let's expand the problem to process a 50 x 51 data set.  Just utilize the original data set:

data want;

set have end=done;

array times {0:50} time_0 - time_50;

array numers {50} numer_1-numer_50;

array denoms {50} denom_1-denom_50;

do i=1 to 50;

   numers{i} + times{i};

   if times{i} > . then denoms{i} + times{i-1};

end;

if done;

length period $ 7;

do i=1 to 50;

   period = cats('Time_', i);

   result = numers{i} / denoms{i};

   output;

end;

keep period result;

run;

It's untested, so there's a possibility it needs a little tweaking.  

[ayin]

Hi Astounding, thanks for your post. Could you please briefly explain what each line means in your code?

Also, how about using multidimensional arrays?

[Astounding]

I can give you the big picture.  But you have to be responsible for knowing basic SAS statements like a DATA statement, a SET statement, END= option, subsetting IF, etc.

The arrays define sets of variables.  The problem could be done without using arrays, but the amount of code would become excessive.  Arrays let you apply a DO loop, processing each variable in the array, with minimal programming.

NUMER_1 will be the sum of all TIME_1 values, NUMER_2 the sum of all TIME_2 values, etc.

DENOM_1 will be the sum of most TIME_0 values, DENOM_2 the sum of most TIME_1 values, etc.  "Most" means that the NUMER variable has to be nonmissing in order to include the value in the DENOM.  For example, DENOM_1 is the sum of all the TIME_0 values where TIME_1 is nonmissing.

Once all those sums have been calculated. perform the final division and output each as a separate observation.