topic Re: Data summary and merging back to original dataset in SAS Programming

Data summary and merging back to original dataset

KDang — Fri, 17 Dec 2010 19:55:59 GMT

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

Re: Data summary and merging back to original dataset

KDang — Fri, 17 Dec 2010 19:56:21 GMT

double post

Re: Data summary and merging back to original dataset

chang_y_chung_hotmail_com — Fri, 17 Dec 2010 20:30:15 GMT

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

Re: Data summary and merging back to original dataset

KDang — Thu, 06 Jan 2011 14:40:30 GMT

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?

Re: Data summary and merging back to original dataset

art297 — Thu, 06 Jan 2011 15:16:56 GMT

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

proc sql noprint;
create table mytablename as
etc
etc

HTH,
Art

Re: Data summary and merging back to original dataset

KDang — Fri, 07 Jan 2011 15:14:39 GMT

Thank you.
Problem solved.