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
- /
- SAS Programming
- /
- SAS Procedures
- /
- Computation

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
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-05-2016 10:30 PM - edited 02-06-2016 02:14 AM

Hi All,

I have a SAS dataset that produces a report for total count based on the year, product and type. Now I want to calculate the ratio from this report. One way I can do is output the SAS report, and do all the ratio calculation in the Excel; however, I'm wondering if there is a way that this ratio calculation can be done in SAS too.

Here is the report:

1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | |||

year | product | type | count | count | count | count | count | count | count | count | count | count | count | count |

2014 | aa | t | 560 | 560 | 687 | 560 | 560 | 560 | 315 | 560 | 245 | 555 | 567 | 566 |

w | 785 | 432 | 450 | 222 | 450 | 333 | 450 | 131 | 321 | 435 | 450 | 454 | ||

x | 160 | 160 | 765 | 160 | 160 | 160 | 676 | 160 | 141 | 325 | 675 | 160 | ||

y | 360 | 360 | 360 | 360 | 360 | 360 | 657 | 121 | 360 | 321 | 360 | 360 | ||

z | 570 | 570 | 493 | 570 | 688 | 976 | 570 | 570 | 153 | 643 | 543 | 848 | ||

bb | x | 160 | 245 | 542 | 160 | 160 | 222 | 334 | 777 | 578 | 545 | 344 | 666 | |

y | 432 | 340 | 360 | 532 | 567 | 360 | 567 | 776 | 454 | 325 | 780 | 455 | ||

cc | p | 555 | 457 | 675 | 988 | 777 | 578 | 324 | 865 | 160 | 643 | 547 | 444 |

And the results I want to get is (column ' roduct' is not in the final output, I list it here to explain which product we need in final result):

month | ||||||||||||||

product: | Catg | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | |

in aa | t / w | 0.71 | 1.30 | 1.53 | 2.52 | 1.24 | 1.68 | 0.70 | 4.27 | 0.76 | 1.28 | 1.26 | 1.25 | |

in aa | w / y | 2.18 | 1.20 | 1.25 | 0.62 | 1.25 | 0.93 | 0.68 | 1.08 | 0.89 | 1.36 | 1.25 | 1.26 | |

in aa | x / y | 0.44 | 0.44 | 2.13 | 0.44 | 0.44 | 0.44 | 1.03 | 1.32 | 0.39 | 1.01 | 1.88 | 0.44 | |

in bb | x / y | 0.37 | 0.72 | 1.51 | 0.30 | 0.28 | 0.62 | 0.59 | 1.00 | 1.27 | 1.68 | 0.44 | 1.46 | |

w in aa y in bb | w / y | 1.82 | 1.27 | 1.25 | 0.42 | 0.79 | 0.93 | 0.79 | 0.17 | 0.71 | 1.34 | 0.58 | 1.00 | |

x in bb p in cc | x / p | 0.29 | 0.54 | 0.80 | 0.16 | 0.21 | 0.38 | 1.03 | 0.90 | 3.61 | 0.85 | 0.63 | 1.50 |

As you see both product 'aa' and 'bb' have type of 'x' and 'y'. I tried transpose type to calculate ratio, but when I transpose back, I lost 2nd 'x/y' which should have product 'bb'. Also the last ratio pick 'x' in product 'aa', not 'bb'.

If we can do this calculation step in SAS, that'll be great. otherwise, we have to manually calculate in the Excel.

I'm attaching the report above, and program here.

Thanks in advance for your help.

**Adding one more question: **

**I also need to calculate rolling 12 month average of the ratio. My real data is 1+ year. For example, **

**When I run December 2014, the average ratio will be Jan 2014 to Dec 2014.**

**When I run January 2015, the average ratio will be Feb 2014 to Jan 2015.**

**Thanks again.**

Accepted Solutions

Solution

02-06-2016
10:21 AM

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

Posted in reply to Belle

02-05-2016 10:43 PM

Those look like hard coded rules.

Not a great solution, and I'd still consider transposing, but an option would be to create a dataset that lists the product and desired numerator/denominator and 'id' for each row. Then merge the dataset with itself by product, numerator and denominator. This will give you the data side by side and you can calculate your ratios using two arrays.

So create a data set like this with your rules, you may need to add year depending on your criteria.

```
PROD_NUM NUM PROD_DEN DEN
aa t aa w
aa w aa y
...
aa w bb y
bb x cc p
```

Then merge the data set with your have dataset, twice once for the numerator data and once for denominator. You'll have to rename the variables. Then calculate your ratio.

```
proc sql;
create table want as
select a.*, b.month1 as num_month1,
...
b.month12 as num_month12,
c.month1 as den_month1,
...
c.month12 as den_month12
```

/*a.month1/b.month1 as ratio1*/ /*get merge right first*/
from rules as a
left join have as b
on a.prod_num=b.product
and a.num=b.type
left join have as c
on a.prod_den=c.product
and a.den=c.type;
quit;

Once you have your data set up, create two arrays and calculate your ratios. Or do it in the step above if you want to elimate a step.

All Replies

Solution

02-06-2016
10:21 AM

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

Posted in reply to Belle

02-05-2016 10:43 PM

Those look like hard coded rules.

Not a great solution, and I'd still consider transposing, but an option would be to create a dataset that lists the product and desired numerator/denominator and 'id' for each row. Then merge the dataset with itself by product, numerator and denominator. This will give you the data side by side and you can calculate your ratios using two arrays.

So create a data set like this with your rules, you may need to add year depending on your criteria.

```
PROD_NUM NUM PROD_DEN DEN
aa t aa w
aa w aa y
...
aa w bb y
bb x cc p
```

Then merge the data set with your have dataset, twice once for the numerator data and once for denominator. You'll have to rename the variables. Then calculate your ratio.

```
proc sql;
create table want as
select a.*, b.month1 as num_month1,
...
b.month12 as num_month12,
c.month1 as den_month1,
...
c.month12 as den_month12
```

/*a.month1/b.month1 as ratio1*/ /*get merge right first*/
from rules as a
left join have as b
on a.prod_num=b.product
and a.num=b.type
left join have as c
on a.prod_den=c.product
and a.den=c.type;
quit;

Once you have your data set up, create two arrays and calculate your ratios. Or do it in the step above if you want to elimate a step.

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

Posted in reply to Reeza

02-06-2016 01:51 AM - edited 02-06-2016 02:14 AM

Hi Reeza,

I tried your method, it works very well. Thanks for your quick response.

I add one more question. I'd really appreciate if you know the answer.

Posting questions here again:

**Adding one more question: **

**I also need to calculate rolling 12 month average of the ratio. My real data is 1+ year. For example, **

**When I run December 2014, the average ratio will be Jan 2014 to Dec 2014.**

**When I run January 2015, the average ratio will be Feb 2014 to Jan 2015.**

Thanks

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

Posted in reply to Belle

02-06-2016 02:39 AM

In general, once your original question has been answered you should mark the question answered and start a new one.

Moving stats are better calculated using a SAS data step and an array. If you've transposed and use PGStats solution then the lag() function is an option.

Here's a reference on calculating moving stats, originally provided by PGStats. It may be shorter...the code below is untested.

```
data want;
set have;
array numRolling(0:11) _temporary_;
array denRolling(0:11) _temporary_;
array num(24) num1-num24;
array den(24) den1-den24;
array ratioRolling(12:24);
do i=1 to dim(num);
numRolling{mod(i,12)} = num(i);
denRolling{mod(i,12)} = den(i);
if i>=12 then do;
ratioRolling(i)=sum(of numRolling(*))/sum(of denRolling(*));
end;
run;
```

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

Posted in reply to Reeza

02-06-2016 10:22 AM

Thanks Reeza. Just marked.

I will test your method. If I'll have any questions, I'll start a new post.

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

Posted in reply to Belle

02-06-2016 12:56 AM - edited 02-06-2016 01:00 AM

SAS can do that for sure

```
data test;
array m month1-month12;
input year product $ type $ month1-month12;
do month = 1 to 12;
count = m{month};
output;
end;
keep year month product type count;
datalines;
2014 aa t 560 560 687 560 560 560 315 560 245 555 567 566
2014 aa w 785 432 450 222 450 333 450 131 321 435 450 454
2014 aa x 160 160 765 160 160 160 676 160 141 325 675 160
2014 aa y 360 360 360 360 360 360 657 121 360 321 360 360
2014 aa z 570 570 493 570 688 976 570 570 153 643 543 848
2014 bb x 160 245 542 160 160 222 334 777 578 545 344 666
2014 bb y 432 340 360 532 567 360 567 776 454 325 780 455
2014 cc p 555 457 675 988 777 578 324 865 160 643 547 444
;
data test_ratio;
input catg &$;
datalines;
t / w
w / y
x / y
x / y
w / y
x / p
;
proc sql;
create table ratio0 as
select a.year, a.month,
catx(" / ", a.product, b.product) as products length=12,
catx(" / ", a.type, b.type) as catg length=8,
a.count/b.count as ratio format=best6.
from test as a inner join test as b
on a.year=b.year and a.month=b.month
where calculated catg in (select catg from test_ratio)
order by year, products, catg, month;
quit;
proc transpose data=ratio0 out=ratio(drop=_name_) prefix=ratio;
by year products catg;
var ratio;
id month;
run;
options linesize=120;
proc print data=ratio noobs; run;
```

PG

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

Posted in reply to PGStats

02-06-2016 02:00 AM

Hi PGStats,

Your method works only if I need the all the combinations.

For example, in your result below, I don't need row 5, 6, and 7.

Thanks

year | products | catg | ratio1 | ratio2 | ratio3 | ratio4 | ratio5 | ratio6 | ratio7 | ratio8 | ratio9 | ratio10 | ratio11 | ratio12 |

2014 | aa / aa | t / w | 0.7134 | 1.2963 | 1.5267 | 2.5225 | 1.2444 | 1.6817 | 0.7 | 4.2748 | 0.7632 | 1.2759 | 1.26 | 1.2467 |

2014 | aa / aa | w / y | 2.1806 | 1.2 | 1.25 | 0.6167 | 1.25 | 0.925 | 0.6849 | 1.0826 | 0.8917 | 1.3551 | 1.25 | 1.2611 |

2014 | aa / aa | x / y | 0.4444 | 0.4444 | 2.125 | 0.4444 | 0.4444 | 0.4444 | 1.0289 | 1.3223 | 0.3917 | 1.0125 | 1.875 | 0.4444 |

2014 | aa / bb | w / y | 1.8171 | 1.2706 | 1.25 | 0.4173 | 0.7937 | 0.925 | 0.7937 | 0.1688 | 0.707 | 1.3385 | 0.5769 | 0.9978 |

2014 | aa / bb | x / y | 0.3704 | 0.4706 | 2.125 | 0.3008 | 0.2822 | 0.4444 | 1.1922 | 0.2062 | 0.3106 | 1 | 0.8654 | 0.3516 |

2014 | aa / cc | x / p | 0.2883 | 0.3501 | 1.1333 | 0.1619 | 0.2059 | 0.2768 | 2.0864 | 0.185 | 0.8813 | 0.5054 | 1.234 | 0.3604 |

2014 | bb / aa | x / y | 0.4444 | 0.6806 | 1.5056 | 0.4444 | 0.4444 | 0.6167 | 0.5084 | 6.4215 | 1.6056 | 1.6978 | 0.9556 | 1.85 |

2014 | bb / bb | x / y | 0.3704 | 0.7206 | 1.5056 | 0.3008 | 0.2822 | 0.6167 | 0.5891 | 1.0013 | 1.2731 | 1.6769 | 0.441 | 1.4637 |

2014 | bb / cc | x / p | 0.2883 | 0.5361 | 0.803 | 0.1619 | 0.2059 | 0.3841 | 1.0309 | 0.8983 | 3.6125 | 0.8476 | 0.6289 | 1.5 |

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

Posted in reply to Belle

02-06-2016 04:56 PM

Simply adapt:

```
data test;
array m month1-month12;
input year product $ type $ month1-month12;
do month = 1 to 12;
count = m{month};
output;
end;
keep year month product type count;
datalines;
2014 aa t 560 560 687 560 560 560 315 560 245 555 567 566
2014 aa w 785 432 450 222 450 333 450 131 321 435 450 454
2014 aa x 160 160 765 160 160 160 676 160 141 325 675 160
2014 aa y 360 360 360 360 360 360 657 121 360 321 360 360
2014 aa z 570 570 493 570 688 976 570 570 153 643 543 848
2014 bb x 160 245 542 160 160 222 334 777 578 545 344 666
2014 bb y 432 340 360 532 567 360 567 776 454 325 780 455
2014 cc p 555 457 675 988 777 578 324 865 160 643 547 444
;
data wantedRatios;
length ratioId $20;
input (p1 p2 t1 t2) ($);
ratioId = catx("@", p1, p2, t1, t2);
keep ratioId;
datalines;
aa aa t w
aa aa w y
aa aa x y
bb bb x y
aa bb w y
bb cc x p
;
proc sql;
create table ratio0 as
select a.year, a.month,
catx(" / ", a.product, b.product) as products length=12,
catx(" / ", a.type, b.type) as catg length=8,
a.count/b.count as ratio format=best6.
from test as a inner join test as b
on a.year=b.year and a.month=b.month
where catx("@", a.product, b.product, a.type, b.type) in
(select ratioId from wantedRatios)
order by year, products, catg, month;
quit;
proc transpose data=ratio0 out=ratio(drop=_name_) prefix=ratio;
by year products catg;
var ratio;
id month;
run;
options linesize=120;
proc print data=ratio noobs; run;
```

PG

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

Posted in reply to PGStats

02-07-2016 10:35 AM

Thanks PGStats, this is the result that I was expecting. Thank you so much.