Date:         Fri, 2 Sep 2005 06:30:59 -0700
Reply-To:     shiling99@YAHOO.COM
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         shiling99@YAHOO.COM
Organization: http://groups.google.com
Subject:      Re: Help on Special Variance Calculation
Comments: To: sas-l@uga.edu
In-Reply-To:  <1125603648.315970.54840@f14g2000cwb.googlegroups.com>
Content-Type: text/plain; charset="iso-8859-1"

Here is a SQL solution.
HTH

data one;
do schoolID=1 to 2;
        n_t=ranuni(123)*10;
        do teachID=1 to n_t;
                        n_s=ranuni(123)*50;
                do studentID=1 to n_s;
                     score=ranuni(11)*100;
                output;
                end;
        end;
end;
run;

proc print; run;

****full data with statistics;
proc sql;
   select a.schoolID, a.teachID , b.teachID, b.studentID, b.score,
          mean(b.score) as mean ,std(b.score) as std
   from (select distinct schoolID, teachID from one) a, one b
   where a.schoolID=b.schoolID
    and  a.teachID ne b.teachID
        group by 1,2
        order by 1, 2, 3, 4
        ;
quit;

****group data statistics;
proc sql;
   select a.schoolID, a.teachID,
          mean(b.score) as mean ,std(b.score) as std
   from (select distinct schoolID, teachID from one) a, one b
   where a.schoolID=b.schoolID
    and  a.teachID ne b.teachID
        group by 1,2
        order by 1, 2
        ;
quit;