@75063:
How do you expect your grand total to be limited to mem in ("C","F") if you're not qualifying the input by these values in the bottom part of the union? Just add the corresponding WHERE clause to it:
proc sql ;
create table Game as
select mem as Member
, count (frequency) as Freq
, sum (percent) as Percent
from have
where mem in ("C", "F")
group mem
union
select "Grand tot" as Member
, count (frequency) as Freq
, sum (percent) as Percent
from have
where mem in ("C", "F")
;
quit ;
Alternatively, it can be done in a DATA step using the hash object. Admittedly, it's more involved programmatically, but it gets there in a single pass over the subset input file (while SQL needs two):
data _null_ ;
dcl hash h (ordered:"A") ;
h.defineKey ("Member") ;
h.defineData ("Member", "Freq", "Percent") ;
h.defineDone () ;
do TF = 1 by 1 until (z) ;
set have (rename=(Percent=P) where=(mem in ("C","F"))) end = z ;
Member = put (mem, $9.) ;
if h.find() then call missing (Freq, Percent) ; /* Note: It means "if NOT found" */
Freq + 1 ;
Percent + P ;
h.replace() ;
TP + P ;
end ;
h.add (key:"Grand tot", data:"Grand tot", data:TF, data:TP) ;
h.output (dataset:"Game") ;
run ;
HTH
Paul D.
... View more
