Date: Thu, 4 Jan 2007 23:25:58 -0800
Reply-To: David L Cassell <davidlcassell@MSN.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: David L Cassell <davidlcassell@MSN.COM>
Subject: Re: FW: Calculating differences between team members
In-Reply-To: <000301c7301c$88869f50$0300a8c0@TARALAPTOP2>
Content-Type: text/plain; format=flowed
twernsing@YAHOO.COM wrote back:
>-----Original Message-----
>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Tara
>Wernsing
>Sent: Thursday, January 04, 2007 9:09 AM
>To: SAS-L@LISTSERV.UGA.EDU
>Subject: Calculating differences between team members
>
>Hi all~
>I am trying to calculate a formula where I need the sum of the squared
>differences between 3 people on a team (I have 24 teams of 3 people) for
>the
>same variable.
>
>so for example, for attitude toward a team project, I need to calculate the
>value of one team member's attitude score subtracted from the second
>member,
>and then subtracted from the third member.
>then square each difference, and sum up all three sq differences.
>
>I can figure out the latter part, but having trouble figuring out how I can
>hold the first person's value in memory, while getting the second person's
>value (could use _point ?) and doing the subtraction but then also a
>needing
>the third person's value (can I use another _point--can't seem to figure
>out
>how in the same do loop)?
>
>any ideas are appreciated! thanks, Tara
>
>Tara Wernsing
>Doctoral Candidate
>University of Nebraska-Lincoln
>Gallup Leadership Institute
> <http://www.gli.unl.edu/> www.gli.unl.edu
>--
>"People take different roads seeking fulfillment and happiness. Just
>because
>they're not on your road doesn't mean they have gotten lost." ~Cesare di
>Bonesana Beccaria
>
>one more complexity to this---I need it to produce an individual level
>score
>for each person (for each ID in my data), not team level score.
>So this score will differ for each person on the team. Below is an excerpt
>from an article that used this measure that explains this in words:
>
>**********************
>Tsui, Egan, Oreilly 1989. Being Different. Administration Science
>Quarterly.
>
>
>The relational demography score is the difference between an individual and
>all other individuals in the work unit (i.e., the sample) on a specific
>demographic attribute, using a formula similar to that used by O'Reilly,
>Caldwell, and Barnett (1989) and by Tsui and O'Reilly (1989). It is the
>square root of the summed squared differences between an individual S,'s
>value on a specific demographic variable and the value on the same variable
>for every other individual Si in the sample for the work unit, divided by
>the total number of respondents in the unit (n). The following formula was
>used for this calculation:
>
>SQRT of (1/n)*[SUM (Si - Sj)**2]
>
>i=target individual member
>J=each other team members
>
>A relational measure was derived for each demographic variable. Differences
>in age, company tenure, and education were measured in years. Differences
>in
>gender and race were measured by a score ranging from zero to approaching
>but never reaching 1.00. For example, a man in a work unit of two men and
>three women would have a relational score of .77 on gender, 0 for being the
>same as the other man and 3 for being different from each of the three
>women. We would then divide the score of 3 by 5 and take the square root of
>the result. Each of the two women, in turn, would have a relational score
>of
>.63. A score of .999 could be obtained by someone who is the sole minority
>member (on either gender or race) in an extremely large group .4
>
>The relational score on race was computed by considering the differences
>among all the racial groups in the work unit. In a work unit with one
>African-American, one Asian, and two whites, the relational score for the
>African-American and the Asian, respectively, would be 3 (1 for being
>different from each other and 2 for being different from each of the two
>whites), 2 for each of the two whites (1 for being different from the
>African-American, 1 for being different from the Asian, and 0 for being
>different from each other).
>
>All the relational measures were scaled in such a way that a large value
>always connotes a large difference. The individual with a large score on a
>relational measure differs more, in terms of that-specific demographic
>attribute, from other individuals in the work unit (i.e., the sample) than
>another individual with a small score. The actual scores observed on the
>relational age and tenure measures ranged from zero to 30 and on the
>relational education measure ranged from zero to 15. The actual scores
>ranged from zero to .99 for both the relational gender and race measures.
Gee Tara, you should have come by and asked me about this last week,
when I was in Lincoln. :-)
One thing that you can do to make this a little easier on yourself is to
realize that this 'complex' formula (which may be TOO complex, if
you ask me) is looking at the variability in the team, plus the difference
between team member J and the team mean. As you have stated
the problem, you want:
>SQRT of (1/n)*[SUM (Si - Sj)**2]
>
>i=target individual member
>J=each other team members
But this is really the same as
SQRT of (1/n)*[SUM (Si - Sj)**2]
i=target individual member
j=ALL team members including team member I
Why? Because I am only adding a zero in to the sum. I
am only adding in the quantity (Si - Si)**2 which has to
be zero.
So that means that the inner part is the same as:
SUM ( Xi - Xj)**2
= SUM (xi-X + X-xJ)**2
= SUM (xi-XBar)**2 + n*(xJ-XBar)**2 + (xJ-XBar)*SUM(xi-XBar)
= SUM (xi-XBar)**2 + n*(xJ-XBar)**2 + 0
[sum of (Xi - mean) has to be zero]
= CSS + n*(xJ-XBar)**2
Where CSS is the 'corrected' sum of squares you use to compute a
sample variance.
So your whole formula becomes:
SQRT of (1/n)*[SUM (Si - Sj)**2]
= sqrt( css/n + (xJ-mean)**2 )
where CSS, N, and MEAN are computed on the team as a unit.
So you could do this as [WARNING: untested code ahead] :
proc sort data=YourData;
by time team;
run;
proc summary data=YourData;
by time team;
var YourVar;
output out=out1 css=cssX n=nX mean=meanX;
run;
data YourOut;
merge YourData out1;
by time team;
score = sqrt( cssX/nX + (YourVar - meanX)**2 );
run;
HTH,
David
--
David L. Cassell
mathematical statistician
Design Pathways
3115 NW Norwood Pl.
Corvallis OR 97330
_________________________________________________________________
Find sales, coupons, and free shipping, all in one place! MSN Shopping
Sales & Deals
http://shopping.msn.com/content/shp/?ctid=198,ptnrid=176,ptnrdata=200639
