Solved
Contributor
Posts: 38

# how to count and get average

Hi all,

Please find the data sample below.

For example, cusip 00001P appears three times, which means that this firm involve in three deals.

What I want is to count how many deals each cusip involves and finally summarize the mean/average of cusip involvement.

Please can anybody help me to deal with this? Thanks.

 dealnum cusip 1394091045 00001P 1455590045 00001P 1455592045 00001P 1175013045 00001Q 215455045 00002A 215912045 00002A 216037045 00002A 235089045 00002P 671514045 00003F 698540045 00003F 876308045 00003F 940272045 00003F 1012615045 00003F 1049688045 00003F 1177658045 00003F 1922076045 00003F 1032109045 00004Y 893020045 00005H 1338024045 00005H

Accepted Solutions
Solution
‎01-01-2015 09:36 AM
Posts: 3,167

## Re: how to count and get average

In case OP meant something different on AVG count:

proc sql;

create table want as

select *, mean(deal_ct) as avg_dlct

from (select cusip, count(distinct dealnum) as deal_ct from have group by cusip);

quit;

All Replies
Posts: 1,147

## Re: how to count and get average

In this case, the average and count of cusip will be same. The below code will give you the count of cusip.

proc sql;

create table want as select *, count(cusip) as count from have group by cusip;

quit;

output:

Hope you are expecting the same.

Thanks,

Jag

Thanks,
Jag
Solution
‎01-01-2015 09:36 AM
Posts: 3,167

## Re: how to count and get average

In case OP meant something different on AVG count:

proc sql;

create table want as

select *, mean(deal_ct) as avg_dlct

from (select cusip, count(distinct dealnum) as deal_ct from have group by cusip);

quit;

Super User
Posts: 8,115

## Re: how to count and get average

The average number of involvements per customer is just the number of involvements divided by the number of customers.

select count(*) / count(distinct cusip) as mean_deal_count

from have

;

Posts: 1,270

## Re: how to count and get average

proc sql;

select cusip,count(cusip) as count_deals,count(cusip)/(select count(*) from have) as average_involvement

from have group by cusip

order by average_involvement desc;

quit;

Contributor
Posts: 38

## Re: how to count and get average

the average I want is "The average number of involvements per customer is just the number of involvements divided by the number of customers.", but your method can be for my future reference. Thanks.

Frequent Contributor
Posts: 102

## Re: how to count and get average

data;   input  case \$10. cusip  \$8.;

cards;

1394091045            00001P

1455590045            00001P

1455592045            00001P

1175013045            00001Q

215455045            00002A

215912045            00002A

216037045            00002A

235089045            00002P

671514045            00003F

698540045            00003F

876308045            00003F

940272045            00003F

1012615045            00003F

1049688045            00003F

1177658045            00003F

1922076045            00003F

1032109045            00004Y

893020045            00005H

1338024045            00005H

proc sort;  by cusip;

data cc;   set;  by cusip;

ct+1;

if last.cusip then do;

output;  ct=0;

end;  drop case;

run;

proc print;  run;

proc means ; var ct; run;

🔒 This topic is solved and locked.