Use of sql should help

Proc sql; Create table abc as Select s, p, t, sum(E*E) / (count(*) - 1) as MSE, Avg(E) as BIAS, Avg(abs(E)) as MAD From esquared Group by s, p, t; Quit;

This will give a data set with the following variable

S, p, t, MSE, BIAS, MAD.

The same dataset could be matched with the initial data by s p and t.

Regards,

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Jindan Zhou Sent: Friday, September 21, 2007 12:33 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Reduce 10 Observations to 1

Hello all!

I am pulling my hair on this one, appreciate any input!

Data structure:

V S P T E1 E2 E3 Class1 60 0.4 0.03 2.46744 2.10776 2.22278 Class2 60 0.4 0.03 0.06885 0.60108 0.28794 Class3 60 0.4 0.03 -0.10300 0.05269 -0.38473 Class4 60 0.4 0.03 -0.05060 -0.06329 0.50464 Class5 60 0.4 0.03 -0.66245 0.38282 0.18169 Class6 60 0.4 0.03 0.08759 0.03235 -0.13544 Class7 60 0.4 0.03 0.27932 0.32019 0.61221 Class8 60 0.4 0.03 -0.23675 -0.05163 -0.09089 Class9 60 0.4 0.03 -0.19958 0.03002 0.54392 Class10 60 0.4 0.03 -0.03957 0.09075 -0.11363 ....... .......

Class1 120 0.8 0.05 2.46744 2.10776 2.22278 Class2 120 0.8 0.05 0.06885 0.60108 0.28794 Class3 120 0.8 0.05 -0.10300 0.05269 -0.38473 Class4 120 0.8 0.05 -0.05060 -0.06329 0.50464 Class5 120 0.8 0.05 -0.66245 0.38282 0.18169 Class6 120 0.8 0.05 0.08759 0.03235 -0.13544 Class7 120 0.8 0.05 0.27932 0.32019 0.61221 Class8 120 0.8 0.05 -0.23675 -0.05163 -0.09089 Class9 120 0.8 0.05 -0.19958 0.03002 0.54392 Class10 120 0.8 0.05 -0.03957 0.09075 -0.11363

Basically for every combination of S, P, and T, there are 10 Class* of V, my final summary would require single summary over Class*,

S P T MSE 60 0.4 0.03 0.11871 ... 120 0.8 0.05 0.23411

In the above example, BIAS is calculate as: For Class1 to Class10: 1. Compute AVG = mean (of E1 E2 E3); 2. Compute TRUE = log((T*(N -1) + exp(-4.6)) / (T*(N-1) + exp(-6.9)), where N =1 if V = Class1, etc.; 3. Computer E = (AVG - TRUE) / TRUE; 4. Finally, MSE = (sum of E squared over Class1 to Class 10) / 9 (other statistic include BIAS = mean of E over Class1 to Class10, MAD = mean of ABS(E) over Class1 to Class10)

Now step 1 through 3 is easy, I managed to computer AVG, TRUE and E for each of the Class*, but I just couldn't figure out how to do the final step: for each combination of S, P, and T, now I have only one statistic left.

My final step is now done in Excel, that's quite some job for a SxPxT = 3x5x7 combination, not to mention I need to alter the parameters...

Once again, Thanks in advance!

Jindan