Desktop productivity for business analysts and programmers

Transpose, mirror/invert, calculation by row

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 82
Accepted Solution

Transpose, mirror/invert, calculation by row

[ Edited ]
/* 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.

 


Accepted Solutions
Solution
‎03-20-2017 06:58 PM
Super User
Posts: 6,903

Re: Transpose, mirror/invert, calculation by row

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.  

View solution in original post


All Replies
Super User
Posts: 23,958

Re: Transpose, mirror/invert, calculation by row

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

Frequent Contributor
Posts: 82

Re: Transpose, mirror/invert, calculation by row

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

Super User
Posts: 6,903

Re: Transpose, mirror/invert, calculation by row

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.

Super User
Posts: 13,889

Re: Transpose, mirror/invert, calculation by row

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.

Solution
‎03-20-2017 06:58 PM
Super User
Posts: 6,903

Re: Transpose, mirror/invert, calculation by row

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.  

Frequent Contributor
Posts: 82

Re: Transpose, mirror/invert, calculation by row

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

Also, how about using multidimensional arrays?
Super User
Posts: 6,903

Re: Transpose, mirror/invert, calculation by row

Posted in reply to 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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 376 views
  • 1 like
  • 4 in conversation