topic Re: how to count and get average in SAS Procedures

how to count and get average

comeon2012 — Thu, 01 Jan 2015 13:47:58 GMT

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

Re: how to count and get average

Jagadishkatam — Thu, 01 Jan 2015 14:17:18 GMT

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

Re: how to count and get average

Haikuo — Thu, 01 Jan 2015 14:36:34 GMT

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;

Re: how to count and get average

Tom — Thu, 01 Jan 2015 16:00:34 GMT

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

;

Re: how to count and get average

stat_sas — Thu, 01 Jan 2015 18:31:10 GMT

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;

Re: how to count and get average

comeon2012 — Fri, 02 Jan 2015 02:44:35 GMT

Thanks Jagadishkatamand stat@sas

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.

Re: how to count and get average

Jim_G — Fri, 02 Jan 2015 16:29:06 GMT

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;