topic Re: grouping some of the members of a variable and adding a grand total in SAS Programming

grouping some of the members of a variable and adding a grand total

75063 — Thu, 23 Aug 2018 05:28:42 GMT

I have a data set and I need to group the type of member "mem" and get a grand total at the bottom. When I am trying to limit the number of members to group, i am still getting the grand total for all the members.

data have;
input mem $ frequency percent;
datalines;
M 240 24.0
M 320 32.0
F 120 12.0
M 80 8.0
C 68 6.8
M 42 4.2
C 130 13.0
;
run;

Proc Sql;
Create table Game as
select mem as Member , count(frequency) as Freq , sum(percent) as Percent
from have
group by mem
having mem in ("C", "F")
union
Select "Grand tot" as mem
,count(frequency) as Freq
,sum(percent) as Percent
from have;
Quit;

The output that I am getting is

Obs Member Freq Percent

1 C 2 19.8

2 F 1 12.0

3 Grand tot 7 100.0

The Grand tot is not giving the value for the selected number of "mem".

I tried by putting the having clause in the second part of the union too, however I did not achive results.

Please suggest a viable option.

Thanking you!

Re: grouping some of the members of a variable and adding a grand total

Jagadishkatam — Thu, 23 Aug 2018 05:43:30 GMT

proc sql;
create table want as select mem length=100,sum(mem in ('F' 'C')) as freq, sum(percent) as percents from have where mem in ('F' 'C') group by mem;
insert into want (mem, freq, percents) 
select 'Grand Total' as mem length=100, count(*) as freq, sum(percent) as percents from have;
quit;

Re: grouping some of the members of a variable and adding a grand total

75063 — Thu, 23 Aug 2018 06:37:53 GMT

many thanks for the guidance, It worked for me. However, just wanted to know if it is possible to do with a "UNION" too (The way i was trying).

Thank you!

Re: grouping some of the members of a variable and adding a grand total

Jagadishkatam — Thu, 23 Aug 2018 07:02:13 GMT

It is possible replace the insert with union as below

proc sql;
create table want as select mem length=100,sum(mem in ('F' 'C')) as freq, sum(percent) as percents from have where mem in ('F' 'C') group by mem
union
select 'Grand Total' as mem length=100, count(*) as freq, sum(percent) as percents from have;
quit;

Re: grouping some of the members of a variable and adding a grand total

hashman — Thu, 23 Aug 2018 07:10:13 GMT

@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.