Count and Sum total

[SASPhile]

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

[novinosrin]

Can it be done in two steps?

[PeterClemmensen]

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;

[novinosrin]

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;

[ballardw]

---

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

[novinosrin]

"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