turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Enterprise Guide
- /
- Transpose, mirror/invert, calculation by row

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-15-2017 02:04 AM - edited 03-17-2017 12:38 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ayin

03-17-2017 05:29 AM

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.

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ayin

03-15-2017 02:21 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

03-17-2017 12:39 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ayin

03-15-2017 03:05 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ayin

03-15-2017 11:17 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ayin

03-17-2017 05:29 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

03-19-2017 07:57 PM

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

Also, how about using multidimensional arrays?

Also, how about using multidimensional arrays?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

03-20-2017 07:29 AM

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.