Date:         Thu, 7 Mar 1996 13:14:07 GMT
Reply-To:     Ziqing Pan <zpan@UMICH.EDU>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         Ziqing Pan <zpan@UMICH.EDU>
Organization: University of Michigan
Subject:      Response to 'silly little question'

/* The table can be obtained by running the following SAS codes on PC
   SAS, version 6.10. I am sure there are other simpler ways to do the
   same job. */
 
data temp;input x y z;
cards;
1 2 2
1 3 1
1 2 2
3 . 2
. 3 2
3 . 2
3 3 2
. 2 .
;
data cat;input cat @@;cards;
1 2 3
;
 
proc sql;
create table tab as select c.cat, xt.x, yt.y, zt.z from cat c
left join (select x as cat, count(*) as x from temp group by x) xt
           on xt.cat=c.cat
left join (select y as cat, count(*) as y from temp group by y) yt
           on yt.cat=c.cat
left join (select z as cat, count(*) as z from temp group by z) zt
           on zt.cat=c.cat;
 
update tab set x=0 where x=.;
update tab set y=0 where y=.;
update tab set z=0 where z=.;
 
create table tab as select *, x+y+z as total from tab;
proc report data=tab nowd headline;
define cat/group;
rbreak after/ol summarize;
run;
 
The output will be:
 
 CAT          X          Y          Z      TOTAL
---------------------------------------------------
  1          3          0          1          4
  2          0          3          6          9
  3          3          3          0          6
         ---------  ---------  ---------  ---------
             6          6          7         19
 
 
Ziqing Pan
Dept. of Biostat.
School of Public Health
University of Michigan
zpan@umich.edu