Date:         Fri, 29 Apr 2005 15:48:25 -0400
Reply-To:     "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
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/