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

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: 8,164

## 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.
Discussion stats
• 5 replies
• 257 views
• 0 likes
• 3 in conversation