Date:         Fri, 18 Jan 2008 13:37:02 -0800
Reply-To:     "Terjeson, Mark" <Mterjeson@RUSSELL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Terjeson, Mark" <Mterjeson@RUSSELL.COM>
Subject:      Re: PROC SQL
Comments: To: Thomas Schmitt <schmitta1573@YAHOO.COM>
In-Reply-To:  A<610845.38128.qm@web82203.mail.mud.yahoo.com>
Content-Type: text/plain; charset="iso-8859-7"

Hi Tom,

You can nest subqueries, but nesting aggregation functions
at different result levels won't get past the compiler. So this
can be rewritten with sql code to handle the couple levels of
scope with nested queries. However, any chance the following
may prove helpful?


    * just as a test of your formulas...... ;
    * substituting your values for group 1 into your formulas ;
data test;
    sample_size = 2;
    sumthem = SUM(1.688835988,1.640304696);
    sum_theta_new = sumthem/sample_size;
    sum_theta_new_diff = SUM((1.688835988 - sum_theta_new)**2);
    Std_error = SQRT(sum_theta_new_diff/(sample_size-1));
put _all_;
run;
/*
sample_size=2
sumthem=3.329140684
sum_theta_new=1.664570342
sum_theta_new_diff=0.0005888216
Std_error=0.024265646
*/

    * this comes up with the same results ;
PROC SQL;
    CREATE TABLE Std_error AS
    SELECT
             theta_group,
             theta_new,
             stderr(theta_new) AS stderr
    FROM
             Std_error_data
    GROUP BY
             theta_group;
QUIT;


.....unless I'm missing something.....




Hope this is helpful.


Mark Terjeson
Senior Programmer Analyst, IM&R
Russell Investments


Russell Investments
Global Leaders in Multi-Manager Investing






-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Thomas Schmitt
Sent: Friday, January 18, 2008 12:07 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: PROC SQL

Hello Group:

I¢m trying to do a calculation in ROC SQL. The data and code is below. The first column is theta_group and the second column is theta_new. Where I run into a problem is in this line: SUM((theta_new-CALCULATED sum_theta_new)**2). How do I subtract each theta_new from the average sum_theta_new and then sum those values?

Best,

Tom




PROC SQL;
 CREATE TABLE Std_error AS
SELECT theta_group ,COUNT(*) AS sample_size, SUM(theta_new)/CALCULATED sample_size AS sum_theta_new,
SUM((theta_new-CALCULATED sum_theta_new)**2) AS sum_theta_new_diff,
SQRT(CALCULATED sum_theta_new_diff/(CALCULATED sample_size-1)) AS Std_error
 FROM Std_error_data
  GROUP BY theta_group;
QUIT;



1 1.688835988
1 1.640304696
2 1.529305114
2 1.799679433
3 2.010606609
3 1.610786296
4 1.710097178
4 1.754939867
5 1.785268613
5 1.61684695


      ____________________________________________________________________________________
Looking for last minute shopping deals?
Find them fast with Yahoo! Search.  http://tools.search.yahoo.com/newsearch/category.php?category=shopping