Data summary and merging back to original dataset

[KDang]

Hi,

My problem is to figure out if an id is above, below or meets the monthly average of invites.

My data looks like the following:

id month invites
1 Jan2010 2
1 Feb2010 3
1 Mar2010 1
2 Jan2010 7
2 Feb2010 1
2 Mar2010 9
3 Jan2010 1
3 Mar2010 2

What I figure are the steps to structure my data correctly is to:
1. calculate monthly means of invites (some id's will be missing months which doesnt need to be included into monthly means)
(in this example Jan2010 =3, Feb2010 =2, Mar2010 =4 )

2. merge monthly means of invites to the larger dataset

3. create a new variable to flag above ( GE 2 points above) or below (LE 2 points below) or meets the mean (+ or - 1 above or below the mean)

So the final data will look something similar to:

id month invites MeanInvites Index
1 Jan2010 2 3 Meets
1 Feb2010 3 2 Meets
1 Mar2010 1 4 Below
2 Jan2010 7 3 Above
2 Feb2010 1 2 Meets
2 Mar2010 9 4 Above
3 Jan2010 1 3 Below
3 Mar2010 2 4 Below

Writing code for step 3 isnt much of a problem as figuring out steps 1 and 2 or if anyone has better suggestions to solve this problem that would be great.

Thanks for your help! Message was edited by: KDang

[KDang]

double post

[chang_y_chung_hotmail_com]

proc sql automatically merges group stats back. HTH.

   /* test data */

   data one;

      input id month :anydtdte7. invites;

      format month monyy7.;

   cards;

   1 Jan2010 2

   1 Feb2010 3

   1 Mar2010 1

   2 Jan2010 7

   2 Feb2010 1

   2 Mar2010 9

   3 Jan2010 1

   3 Mar2010 2

   ;

   run;

   proc format;

     value idxf low--2="Below" -2<-<2="Meets" 2-high="Above";

   run;

   proc sql;

     select one.*

          , mean(invites) as meanInvites format=8.2

          , putn(invites - calculated meanInvites,"idxf") as index 

     from one

     group by month

     order by id, month;

   quit;

   /* on lst

                                     mean

         id    month   invites    Invites  index

   ----------------------------------------------

          1  JAN2010         2       3.33  Meets

          1  FEB2010         3       2.00  Meets

          1  MAR2010         1       4.00  Below

          2  JAN2010         7       3.33  Above

          2  FEB2010         1       2.00  Meets

          2  MAR2010         9       4.00  Above

          3  JAN2010         1       3.33  Below

          3  MAR2010         2       4.00  Below

   */

[KDang]

Perfect, thanks this worked.

how would i change the sql code so that instead of producing a report it produces another data table for me?

[art297]

Just change the calling line and add one line. e.g.:

proc sql noprint;
create table mytablename as
etc
etc

HTH,
Art

[KDang]

Thank you.
Problem solved.