Date:         Mon, 21 Jan 2008 10:57:47 -0800
Reply-To:     Thomas Schmitt <schmitta1573@YAHOO.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Thomas Schmitt <schmitta1573@YAHOO.COM>
Subject:      Re: PROC SQL
Content-Type: text/plain; charset=utf-8

Hello Group:
 
Thank you Sigurd and Mark for you insightful suggestions. Below is the solution I used.
 
Best,
 
Tom
 
DATA Std_error_data;
SET combined_data;
PROC SQL;
CREATE TABLE Std_error AS
SELECT theta_group ,sample_size, sum_theta_new,
SUM((theta_new-sum_theta_new)**2) AS sum_theta_new_diff,
SQRT(CALCULATED sum_theta_new_diff/(sample_size-1)) AS Std_error
FROM
(SELECT theta_group ,theta_new, COUNT(*) AS sample_size, SUM(theta_new)/CALCULATED sample_size AS sum_theta_new
FROM Std_error_data
GROUP BY theta_group)
GROUP BY theta_group;
QUIT;



----- Original Message ----
From: Sigurd Hermansen <HERMANS1@WESTAT.com>
To: Thomas Schmitt <schmitta1573@yahoo.com>; SAS-L@LISTSERV.UGA.EDU
Sent: Friday, January 18, 2008 4:56:42 PM
Subject: RE: PROC SQL


Thomas:
I don't see how the SORT operator/function fits into a SQL SELECT clause. A more serious problem, though, appears in the nesting of SUM functions (whether CALCULATED or not). SAS PROC SQL does not allow that. Try moving the inner SUM to an in-line view or sub=query.
S



From: owner-sas-l@listserv.uga.edu on behalf of Thomas Schmitt
Sent: Fri 1/18/2008 3: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


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