topic Re: count the number of occurences in SAS Procedures

count the number of occurences

sasphd — Wed, 10 Jan 2018 18:58:48 GMT

Before Asking for my problem here I have mistake in my table here the format of the data is not good to give the table I want to present for you. please correct it before.

My problem now is to count the number of CRSP_fundno that have the same MGMT_NAME for each CALDT??????????????

Table I have is

data family;
input Rank_NO crsp_fundno MGMT_NAME CALDT;
format MGMT_NAME $80. caldt YYMMDDN8.;
cards;

1 53 SCUDDER STEVENS & CLARK 19921231
2 105 AMERICAN PENSION INVESTORS INC. 19921231
3 113 WINSBURY COMPANY (THE) 19921231
5 131 WINSBURY COMPANY (THE) 19921231
9 309 CAPSTONE FINANCIAL SERVICES 19921231
11 606 KEYSTONE GROUP INC. 19921231
14 720 OHIO COMPANY (THE) 19921231
15 828 INVESCO FUNDS GROUP INC. 19921231
17 871 SIGNATURE FINANCIAL GROUP INC. 19921231
532 7235 CENTURY SHARES TRUST 19931231
536 7317 PACIFIC FINANCIAL RESEARCH 19931231
537 7353 WANGER ASSET MANAGEMENT L.P. 19931231
539 7374 COLUMBIA FUNDS MANAGEMENT CO. 19931231
540 7405 COLONIAL MANAGEMENT ASSOCIATES INC. 19931231
541 7406 COLONIAL MANAGEMENT ASSOCIATES INC. 19931231
543 7428 COLONIAL MANAGEMENT ASSOCIATES INC. 19931231
;
run;

the table I want is

MGMT_NAME	CALDT	number
SCUDDER STEVENS & CLARK	1992-12-31	1
AMERICAN PENSION INVESTORS INC.	1992-12-31	1
WINSBURY COMPANY (THE)	1992-12-31	2
CAPSTONE FINANCIAL SERVICES	1992-12-31	1
KEYSTONE GROUP INC.	1992-12-31	1
OHIO COMPANY (THE)	1992-12-31	1
INVESCO FUNDS GROUP INC.	1992-12-31	1
SIGNATURE FINANCIAL GROUP INC.	1992-12-31	1
CENTURY SHARES TRUST	1993-12-31	1
PACIFIC FINANCIAL RESEARCH	1993-12-31	1
WANGER ASSET MANAGEMENT L.P.	1993-12-31	1
COLUMBIA FUNDS MANAGEMENT CO.	1993-12-31	1
COLONIAL MANAGEMENT ASSOCIATES INC.	1993-12-31	3

Re: count the number of occurences

novinosrin — Wed, 10 Jan 2018 19:15:06 GMT

data family;
input Rank_NO crsp_fundno MGMT_NAME & $50. CALDT : yymmdd10.;
format caldt YYMMDD10.;
cards;
1 53 SCUDDER STEVENS & CLARK 19921231
2 105 AMERICAN PENSION INVESTORS INC. 19921231
3 113 WINSBURY COMPANY (THE) 19921231
5 131 WINSBURY COMPANY (THE) 19921231
9 309 CAPSTONE FINANCIAL SERVICES 19921231
11 606 KEYSTONE GROUP INC. 19921231
14 720 OHIO COMPANY (THE) 19921231
15 828 INVESCO FUNDS GROUP INC. 19921231
17 871 SIGNATURE FINANCIAL GROUP INC. 19921231
532 7235 CENTURY SHARES TRUST 19931231
536 7317 PACIFIC FINANCIAL RESEARCH 19931231
537 7353 WANGER ASSET MANAGEMENT L.P. 19931231
539 7374 COLUMBIA FUNDS MANAGEMENT CO. 19931231
540 7405 COLONIAL MANAGEMENT ASSOCIATES INC. 19931231
541 7406 COLONIAL MANAGEMENT ASSOCIATES INC. 19931231
543 7428 COLONIAL MANAGEMENT ASSOCIATES INC. 19931231
;
run;

proc sort data=family out=_have;
by CALDT MGMT_NAME;
run;

data want;
set _have;
by CALDT MGMT_NAME;
if first.MGMT_NAME then count=1;
else count+1;
if last.MGMT_NAME;
keep MGMT_NAME CALDT count;
run;

Re: count the number of occurences

novinosrin — Wed, 10 Jan 2018 19:18:08 GMT

proc sql;
create table want as
select CALDT,MGMT_NAME, count(MGMT_NAME) as count
from family
group by CALDT,MGMT_NAME ;
quit;

Re: count the number of occurences

Shmuel — Wed, 10 Jan 2018 19:19:56 GMT

You can use proc freq:

proc sort data=have; by caldt mgmt_name; run;
proc freq data=have;
 by caldt;
     table mgmt_name;
run;

Alternatively you can run:

proc sort data=have; by caldt mgmt_name; run;

data want;
 set have;
      by caldt mgmt_name;
      retain count;
      if first.mgmt_name then count=0;
      count+1;
     if last.mgmt_name then output;
run;