```Date: Fri, 29 Apr 2005 15:48:25 -0400 Reply-To: "Richard A. DeVenezia" Sender: "SAS(r) Discussion" From: "Richard A. DeVenezia" Subject: Re: Sum Comments: To: sas-l@uga.edu Thomas wrote: > Hi all, > > May I know how to sum the following variables and then do the average > across it? The -999 and . should be excluded from the summing and > averaging. > > Thank you very much! > > Procedures: > 1. sum across x and y separately. > 2. each individual x divided by sum_x from No.1 (each individual y > divided by sum_y). > 3. square each individual from the NO.2 > 4. sum again the No.3 across x and y, respectively. > 4. -999 and . are excluded here (treated as missing values). > > /*expected data*/ > id x1 x2 x3 x4 y1 y2 y3 y4 y5 > 1 100 50 -999 . 20 22 23 25 30 > 2 -999 0 40 -999 . -999 40 -999 . > 3 100 . -999 . 50 0 . . -999 > > /*expected output */ > id index_x index_y; > 1 0.5556 [(100/150)^2] + [(50/150)^2] 0.204 [the cal. same as > var x] 2 1 [(0/40)^2] + [(40/40)^2 1 > 3 1 (100/100)^2 1 Forget about data steps, arrays and do loops. SQL is the best way ;) ;) ------------------------ /* Richard A. DeVenezia, 4.29.05 */ proc format; invalue foo -999=. ; run; data foo; input id ( x1 x2 x3 x4 y1 y2 y3 y4 y5 ) (& foo.); cards; 1 100 50 -999 . 20 22 23 25 30 2 -999 0 40 -999 . -999 40 -999 . 3 100 . -999 . 50 0 . . -999 ; run; proc transpose data=foo out=foot prefix=z; by id; run; proc sql; create table bart as select id, array, sum(xt**2) as result from (select id , substr(_name_,1,1) as array , z1 / sum(z1) as xt from foot group by id,array ) group by id,array ; proc transpose data=bart out=bar; by id; id array; var result; run; ------------------------ Richard A. DeVenezia -- Learn how to customize SAS Explorer http://www.devenezia.com/downloads/sas/actions/ ```

Back to: Top of message | Previous page | Main SAS-L page