Desktop productivity for business analysts and programmers

Transpose, mirror/invert, calculation by row

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 80
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
Respected Advisor
Posts: 4,993

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
Grand Advisor
Posts: 17,396

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: 80

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.

Respected Advisor
Posts: 4,993

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.

Grand Advisor
Posts: 10,223

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
Respected Advisor
Posts: 4,993

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: 80

Re: Transpose, mirror/invert, calculation by row

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

Also, how about using multidimensional arrays?
Respected Advisor
Posts: 4,993

Re: Transpose, mirror/invert, calculation by row

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
  • 213 views
  • 1 like
  • 4 in conversation