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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.