counting observations in by group and then assigning those observations with the total count

[mallorybane]

Hi all!

I know how to count observations by group, for example:

data temp;
  set dataset;
  by keyvar;
  length per 8.;
  if first.keyvar then per=0;
  per+1;
run;

This code returns output that looks like this:

keyvar      per(count)

a	1
b	1
b	2
b	3
c	1
c	2
c	3
c	4

Instead I want the output to look like this:

keyvar     per(count)

a	1
b	3
b	3
b	3
c	4
c	4
c	4
c	4

Does anyone have any ideas for me?

Thanks! Hope to hear from people!

[PaigeMiller]

PROC FREQ will count the observations by group, then you can merge the results with the original data. Or PROC SQL can do this as well.

proc freq data=have;
     tables keyvar/out=counts;
run;
data want;
    merge have counts;
    by keyvar;
run;

[mallorybane]

thanks for this! do you have an example with proc sql?

[novinosrin]

Hello @mallorybane 

data have;
input keyvar  $ ;
cards;
a	1
b	1
b	2
b	3
c	1
c	2
c	3
c	4
;

proc sql;
create table want as
select a.*,count
from 
have a, (select keyvar, count(keyvar)as count from have group by keyvar) b
where a.keyvar=b.keyvar
order by a.keyvar;
quit;

[novinosrin]

And if you are using SAS 9.4

data _null_ ;
if _n_=1 then do;
   dcl hash H (ordered: "A",multidata:'y') ;
   h.definekey  ("keyvar") ;
   h.definedata ("keyvar", "count") ;
   h.definedone () ;
end;
do count=1 by 1 until(last.keyvar);
set have end=lr;
by keyvar;
rc=h.add();
end;
rc=h.replace();
if lr then h.output(dataset:'want')	;
run;

[novinosrin]

Simple double DOW, prolly the fastest

data want ;
if 0 then set have;
do count=1 by 1 until(last.keyvar);
set have ;
by keyvar;
end;
do until(last.keyvar);
set have;
by keyvar;
output;
end;
run;

[novinosrin]

Using FIRST and LAST but interleave

data want;
set have(in=a) have(in=b);
by keyvar;
if first.keyvar then count=0;
if a then count+1;
if b then output;
run;

[PGStats]

Very nice @novinosrin !

[novinosrin]

Thank you Sir @PGStats . Hopefully as good as you one day however a farfetched dream as of now. Merci beacoup!

[Ksharp · Accepted solution]

Make a dummy variable.

data have;
input keyvar  $ ;
dummy=1;
cards;
a	1
b	1
b	2
b	3
c	1
c	2
c	3
c	4
;

proc sql;
create table want(drop=dummy) as
 select *,count(*) as count
  from have
   group by keyvar;
 quit;