DATA Step, Macro, Functions and more

Data summary and merging back to original dataset

Reply
Contributor
Posts: 27

Data summary and merging back to original dataset

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
Contributor
Posts: 27

Re: Data summary and merging back to original dataset

double post
Regular Contributor
Posts: 241

Re: Data summary and merging back to original dataset

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


   */

Contributor
Posts: 27

Re: Data summary and merging back to original dataset

Posted in reply to chang_y_chung_hotmail_com
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?
PROC Star
Posts: 7,492

Re: Data summary and merging back to original dataset

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

proc sql noprint;
create table mytablename as
etc
etc

HTH,
Art
Contributor
Posts: 27

Re: Data summary and merging back to original dataset

Thank you.
Problem solved.
Ask a Question
Discussion stats
  • 5 replies
  • 206 views
  • 0 likes
  • 3 in conversation