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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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