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.       ... View more

Hello, I want to get each id with non-missing y and z. but if I have highest x with non-missing y or z, then I will keep them. My approach is if I can replace missing x and y to the previous no-missing value, then at the end, I can just keep last.id. However, I'm having difficulty to replace these missing value. I try lag, but doesn't work well. I appreciate any help. data temp;    input id x y z;    cards;    1 2 3 2    1 4 5 .    1 4 . 4    1 4 . 7    2 6 4 2    2 6 8 .    2 7 . 8    2 7 9 .    2 9 . .    3 4 2 3    3 4 4 .    3 7 . 9    3 7 . 6    4 2 . 1    4 3 4 2    ; run; The result I expect is: id    x     y     z; 1    4     5      7 2    7     9      8 3    7     4      6 4    3     4      2 ... View more