BookmarkSubscribeRSS Feed
KDang
Fluorite | Level 6
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
5 REPLIES 5
KDang
Fluorite | Level 6
double post
chang_y_chung_hotmail_com
Obsidian | Level 7
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
Fluorite | Level 6
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
Opal | Level 21
Just change the calling line and add one line. e.g.:

proc sql noprint;
create table mytablename as
etc
etc

HTH,
Art
KDang
Fluorite | Level 6
Thank you.
Problem solved.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 2423 views
  • 0 likes
  • 3 in conversation