Date:         Thu, 23 Dec 2004 12:57:14 -0600
Reply-To:     "Dunn, Toby" <Toby.Dunn@TEA.STATE.TX.US>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Dunn, Toby" <Toby.Dunn@TEA.STATE.TX.US>
Subject:      Re: column-wise sum() in proc sql?
Comments: To: "Pardee, Roy" <pardee.r@GHC.ORG>
Content-Type: text/plain; charset="iso-8859-1"

Roy,
 
Are you refering to something like the following:
 
DATA ONE;                       
 DO X = 1 TO 10;                
   Y = 2;                       
   OUTPUT;                      
 END;                           
RUN;                            
                                
PROC SQL;                       
CREATE TABLE SUM AS             
 SELECT * , SUM(X,Y) AS ACROSS  
  FROM ONE;                     
QUIT;                           
                                
PROC PRINT DATA = SUM;          
RUN;                            
 
hth
Toby 

________________________________

From: SAS(r) Discussion on behalf of Pardee, Roy
Sent: Thu 12/23/2004 11:10 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: column-wise sum() in proc sql?



Hey All,

Is it possible to use sas' sum() function to do a sum across columns in
proc sql?  I thought I'd read somewhere that when parsing selects that
include functions like sum(), sas figures out from whether or not
there's a var list inside the parens whether to do a sas-datastep-style
sum across columns, or rather a sql-style aggregate sum across rows.

Neither of these are working for me.  Am I hallucinating the feature
described above?  Or maybe it's a v9 thing (I'm running v8.2)?

proc sql ;
   create table bsrr as
   select studyid
         , infodate
         , txremevr as EverBiopsy
         , sum(of father brother son mother sister daughter) as
Num1stDeg
         , sum(of aunt grmother) as Num2dDeg
   from bsrr.bsrrupdt as b inner join
         ditpc.xwalk as x
   on    b.chsid = x.chsid
   where surveydt between "&Start"d and "&End"
   ;
   create table bsrr as
   select studyid
         , infodate
         , txremevr as EverBiopsy
         , sum(of father, brother, son, mother, sister, daughter) as
Num1stDeg
         , sum(of aunt, grmother) as Num2dDeg
   from bsrr.bsrrupdt as b inner join
         ditpc.xwalk as x
   on    b.chsid = x.chsid
   where surveydt between "&Start"d and "&End"
   ;
end ;

Thanks!

-Roy

Roy Pardee
Research Analyst/Programmer
Center For Health Studies (Cancer Research Network)
Group Health Cooperative
(206) 287-2078

This message and any attached files might contain confidential
information protected by federal and state law. The information is
intended only for the use of the individual(s) or entities originally
named as addressees. The improper disclosure of such information may be
subject to civil or criminal penalties. If this message reached you in
error, please contact the sender and destroy this message. Disclosing,
copying, forwarding, or distributing the information by unauthorized
individuals or entities is strictly prohibited by law.