Good morning,
I am trying to create 3 year moving average rates from a large birth file. I have used proc summary to get the data into a format to create the rates by year
proc summary data=ptm_denom;
class year;
var preterm birth;
output out=ptmtab sum=preterm birth;
run;
proc print data=ptmtab;
run;
data ptm_rate (drop = _type_ _freq_);
set ptmtab;
ptmrate = (preterm/birth)*100;
run;
proc print data=ptm_rate noobs;
var year preterm birth ptmrate;
run;
And my output looks like this:
year preterm birth ptmrate
. | 3986 | 52025 | 7.66170 |
2007 | 439 | 5549 | 7.91134 |
2008 | 453 | 5627 | 8.05047 |
2009 | 453 | 5517 | 8.21098 |
2010 | 453 | 5298 | 8.55040 |
2011 | 394 | 5082 | 7.75285 |
2012 | 363 | 5069 | 7.16118 |
2013 | 368 | 4946 | 7.44036 |
2014 | 374 | 5031 | 7.43391 |
2015 | 328 | 4977 | 6.59032 |
2016 | 361 | 4929 | 7.32400 |
but I can't figure out how to sum and average the data and manipulate the table so my output looks like this:
2007-2009 | 2008-2010 | 2009-2011 | 2010-2012 | 2011-2013 | 2012-2014 | 2013-2015 | 2014-2016 | |
preterm | ||||||||
birth | ||||||||
ptmrate |
This seems like there would be an easy solution, but I can't figure one out. Any help would be great!
PROC EXPAND (if you have it in your SAS license) can provide 3 year moving averages of your data.
Since your output is vertical but the table you want is horizontal, you could transpose your output, then use ARRAYs in a data step to get three year moving averages.
But just a word to the wise ... keep your final table vertical, not horizontal, it's a much easier format for SAS to work with, and much easier for people to read.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.