Help using Base SAS procedures

count the number of occurences

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 126
Accepted Solution

count the number of occurences

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

 


Accepted Solutions
Solution
‎01-10-2018 03:15 PM
PROC Star
Posts: 1,833

Re: count the number of occurences

Posted in reply to novinosrin

 

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

View solution in original post


All Replies
PROC Star
Posts: 1,833

Re: count the number of occurences

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;

Solution
‎01-10-2018 03:15 PM
PROC Star
Posts: 1,833

Re: count the number of occurences

Posted in reply to novinosrin

 

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

Trusted Advisor
Posts: 1,837

Re: count the number of occurences

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 159 views
  • 2 likes
  • 3 in conversation