@Patrick: Thank you so much for your assistance. I know now what I need to do to get the data typed out. I appreciate it. ... View more

@PaigeMiller My apologies for not providing the data in that manner, I am very new to sas. This code worked perfectly...THANK YOU SO MUCH!!!! ... View more

This is the code I used to go from individual to aggregate by year/month: proc sql; create table sqfagg as select put(sqfmthyr, YYMMN6.) as reporting_period, tract2010, count (*) as stopcount, sum(frisk) as friskcount format=comma10. , sum(furtive) as furtcount format=comma10. , sum(search) as searchcount format=comma10. , count(*) as stopcount format=comma10., sum(arrestmade) as arrestcount format=comma10., sum(summonissue) as summonscount format=comma10., sum(physicalforce) as phyfrc format=comma10., sum(criminaldress) format=comma10. as crimdress, sum(black) format=comma10. as blackcount, sum(white) format=comma10. as whitecount, sum(hispanic) format=comma10. as hispaniccount, sum(otherrace) format=comma10. as othracecount from sqfmonthyear group by tract2010, put(sqfmthyr, YYMMN6.) ; quit;   This works, but now I need to aggregate to 6 month intervals but a rolling 6 months so 1/2006-6/2006, 2/2006-7/2006, 3/2006-8/2006 etc. @PaigeMiller I have reviewed your recommendations of how to post my question; however, I do not have code that is not working, I am trying to figure out where to start. And I simply trying to get some insight of different methods to try because I am stuck. Your instructions are not helping me.     ... View more

This worked perfectly! Thanks!!!! ... View more

Here is what I have: sqfmthyr tract2010 friskcount furtcount searchcount arrestcount Feb-06 36005000200 1 0 0 0 Feb-06 36005000200 0 0 0 0 Feb-06 36005000200 1 0 0 0 Mar-06 36005000200 1 0 0 0 Apr-06 36005000200 1 0 0 0 May-06 36005000200 1 0 0 0 May-06 36005000200 0 2 0 0 Jun-06 36005000200 2 2 0 0 Jun-06 36005000200 1 1 0 1 Jun-06 36005000200 1 1 0 0 Jun-06 36005000200 1 1 0 0 Jul-06 36005000200 2 0 0 0 Jul-06 36005000200 2 0 0 0 Jul-06 36005000200 0 0 0 0 Aug-06 36005000200 2 2 1 1 Aug-06 36005000200 1 0 0 1 Sep-06 36005000200 3 3 0 0 Oct-06 36005000200 0 0 0 0   Here is what I need:   sqfmthyr sqfmthyragg tract2010 friskcount furtcount searchcount arrestcount 200602 200602-200607 36005000200 14 7 0 1 200603 200603-200608 36005000200 15 9 1 3 200604 200604-200609 36005000200 17 12 1 3 200605 200605-200610 36005000200 16 12 1 3 ... View more

Data I have SQFMTHYR            Tract2010        Friskcount          Furtcount               Searchcount             Arrestcount 200801                   36005000200       1                          0                             1                             0 200802                   36005000200       1                          0                             1                             0 200803                   36005000200       1                          0                             1                             0 200804                   36005000200       1                          0                             1                             0 200805                   36005000200       1                          0                             1                             0 200806                   36005000200       1                          0                             1                             0 200807                   36005000200       1                          0                             1                             0     Data I want SQFMTHYR       SQFMTHYR6A     Tract2010        Friskcount          Furtcount               Searchcount             Arrestcount 200801               200801-200806     36005000200       6                          0                             6                             0 200802               200802-200807     36005000200       6                          0                             6                             0   I don't really need the SQFMTHYR6A this is just designating that this aggregate is the six months after 01/2008 and after 02/2008. I need these 6 month aggregates for each month/year timeframe and I have 6 aggregates per month/year timeframe (see above). I have ideas on how to do it and have it visualized but am at a total loss with regard to programming.   ... View more

138 139 proc sql; 140 create table sqfagg as 141 select sqfmthyr, tract2010, count (*) as stopcount, sum(frisk) as friskcount 141! format=comma10. , sum(furtive) as furtcount format=comma10. , 142 sum(search) as searchcount format=comma10. , sum(arrestmade) as arrestcount 142! format=comma10., 143 sum(summonissue) as summonscount format=comma10., sum(physicalforce) as phyfrc 143! format=comma10., 144 sum(criminaldress) format=comma10. as crimdress 145 from sqfmonthyear 146 group by tract2010, sqfmthyr ; NOTE: Table WORK.SQFAGG created, with 1656327 rows and 10 columns. 147 quit; NOTE: PROCEDURE SQL used (Total process time): real time 1.46 seconds cpu time 1.42 seconds   Sorry, this is the first time I have asked a question. SQFMTHYR is a date (date YYYYMM format)   Here is the data, the first three rows should have been combined in the sum/count, so stopcount=3, friskcount=2, summonscount=1, phyfrc=3, other variables=0.           ... View more

Here is the log: 138 139 proc sql; 140 create table sqfagg as 141 select sqfmthyr, tract2010, count (*) as stopcount, sum(frisk) as friskcount 141! format=comma10. , sum(furtive) as furtcount format=comma10. , 142 sum(search) as searchcount format=comma10. , sum(arrestmade) as arrestcount 142! format=comma10., 143 sum(summonissue) as summonscount format=comma10., sum(physicalforce) as phyfrc 143! format=comma10., 144 sum(criminaldress) format=comma10. as crimdress 145 from sqfmonthyear 146 group by tract2010, sqfmthyr ; NOTE: Table WORK.SQFAGG created, with 1656327 rows and 10 columns. 147 quit; NOTE: PROCEDURE SQL used (Total process time): real time 1.46 seconds cpu time 1.42 seconds     It shows as if it is working without errors, but I should only have ~200K rows of data if it aggregates correctly   ... View more