topic Re: Count and Sum total in New SAS User

Count and Sum total

SASPhile — Mon, 01 Apr 2019 18:08:20 GMT

Id
a
b
c
a
b
a
b
c
b

How to get counts of id and then sum the total number of id's and display the below format in a table?

a b c total
3 4 2 9

Re: Count and Sum total

novinosrin — Mon, 01 Apr 2019 18:26:20 GMT

Can it be done in two steps?

Re: Count and Sum total

PeterClemmensen — Mon, 01 Apr 2019 18:27:16 GMT

One way

data have;
input Id $;
datalines;
a
b
c
a
b
a
b
c
b
;

proc freq data=have;
   tables Id / out=temp;
run;

proc transpose data=temp out=tempwide;
    id Id;
    var count;
run;

data want;
   set tempwide(keep=_NUMERIC_);
   total=sum(of _NUMERIC_);
run;

Re: Count and Sum total

novinosrin — Mon, 01 Apr 2019 19:10:35 GMT


data have;
input Id $;
cards;
a
b
c
a
b
a
b
c
b
;

/*Need this step for horizontal variables names*/
proc sql noprint;
select distinct id,count(distinct id)  into :vars separated by ' ',:c
from have;
quit;
/*straight forward look up and count*/
data want;
set have end=lr;
array names(&c) &vars;
array temp(&c) $ _temporary_ ;
if _n_=1 then do _n_=1 to dim(temp);
temp(_n_)=vname(names(_n_));
end;
names(whichc(id,of temp(*)))+1;
if lr;
total=sum(of names(*));
output;
drop id;
run;

Re: Count and Sum total

ballardw — Mon, 01 Apr 2019 19:25:10 GMT

@SASPhile wrote:

Id
a
b
c
a
b
a
b
c
b

How to get counts of id and then sum the total number of id's and display the below format in a table?

a b c total
3 4 2 9

How about:

data have;
input Id $;
datalines;
a
b
c
a
b
a
b
c
b
;
run;

proc tabulate data=have;
   class id;
   table n='',
         id='' All='Total'
         /row=float
   ;
quit;

Re: Count and Sum total

novinosrin — Mon, 01 Apr 2019 19:27:55 GMT

"Speak of the SAS genie", I was just mentioning to a mate that you will come up with a proc tabulate and you did. 🙂 Kudos to me for predicting haha and you lol