topic Re: Transpose, mirror/invert, calculation by row in SAS Enterprise Guide

Transpose, mirror/invert, calculation by row

ayin — Fri, 17 Mar 2017 04:38:02 GMT

/* 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.

Re: Transpose, mirror/invert, calculation by row

Reeza — Wed, 15 Mar 2017 06:21:53 GMT

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

Re: Transpose, mirror/invert, calculation by row

Astounding — Wed, 15 Mar 2017 07:05:00 GMT

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.

Re: Transpose, mirror/invert, calculation by row

ballardw — Wed, 15 Mar 2017 15:17:37 GMT

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.

Re: Transpose, mirror/invert, calculation by row

ayin — Fri, 17 Mar 2017 04:39:23 GMT

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

Re: Transpose, mirror/invert, calculation by row

Astounding — Fri, 17 Mar 2017 09:29:50 GMT

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.

Re: Transpose, mirror/invert, calculation by row

ayin — Sun, 19 Mar 2017 23:57:38 GMT

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

Also, how about using multidimensional arrays?

Re: Transpose, mirror/invert, calculation by row

Astounding — Mon, 20 Mar 2017 11:29:02 GMT

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.