topic Re: counting observations in by group and then assigning those observations with the total count in New SAS User

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

mallorybane — Thu, 16 May 2019 20:03:12 GMT

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!

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

PaigeMiller — Thu, 16 May 2019 20:06:26 GMT

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;

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

mallorybane — Thu, 16 May 2019 20:27:38 GMT

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

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

novinosrin — Thu, 16 May 2019 20:32:02 GMT

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;

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

novinosrin — Fri, 17 May 2019 13:10:04 GMT

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;

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

novinosrin — Thu, 16 May 2019 21:02:44 GMT

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;

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

novinosrin — Thu, 16 May 2019 21:06:15 GMT

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;

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

PGStats — Fri, 17 May 2019 03:36:58 GMT

Very nice @novinosrin !

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

novinosrin — Fri, 17 May 2019 03:38:31 GMT

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

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

Ksharp — Fri, 17 May 2019 14:16:43 GMT

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;