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!
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;
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;
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!
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.