topic count by multiple groups in New SAS User

count by multiple groups

dapenDaniel — Thu, 24 Oct 2019 22:00:37 GMT

Hi, I have a dataset like input below.

FirmID Year indicator

10001 1995 1

10001 1996 0

10001 1997 0

10001 1997 1

10001 1998 1

10001 1999 0

10001 1999 1

10001 2000 0

10001 2001 1

I would like to count how many 0 for each company in each year. The output is like below.

FirmID Year nb_Indicator_0

10001 1995 0

10001 1996 1

10001 1997 1

10001 1998 0

10001 1999 2

10001 2000 1

10001 2001 0

what code do I need to use? Thanks!

Re: count by multiple groups

PaigeMiller — Thu, 24 Oct 2019 22:05:39 GMT

@dapenDaniel wrote:

Hi, I have a dataset like input below.

FirmID Year indicator

10001 1995 1

10001 1996 0

10001 1997 0

10001 1997 1

10001 1998 1

10001 1999 0

10001 1999 0

10001 1999 1

10001 2000 0

10001 2001 1

I would like to count how many 0 for each company in each year. The output is like below.

FirmID Year nb_Indicator_0

10001 1995 0

10001 1996 1

10001 1997 1

10001 1998 0

10001 1999 2

10001 2000 1

10001 2001 0

what code do I need to use? Thanks!

data have2;
    set have;
    zero = (nb_indicator=0);
run;
proc summary data=have2 nway;
    class year;
    var zero;
    output out=want sum=count_zero;
run;

Re: count by multiple groups

novinosrin — Thu, 24 Oct 2019 22:12:54 GMT

Hi @dapenDaniel

data have;
input FirmID        Year        indicator;
cards;
10001        1995          1
10001        1996          0
10001        1997          0
10001        1997          1
10001        1998          1
10001        1999          0
10001        1999          0
10001        1999          1
10001        2000          0
10001        2001          1
;

proc sql;
create table want as
select firmid, year, sum(indicator=0) as nb_Indicator_0
from have
group by firmid, year;
quit;

/*or*/

data want;
do until(last.year);
 set have;
 by FirmID Year;
 nb_Indicator_0=sum(indicator=0,nb_Indicator_0);
end;
drop indicator;
run;