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 |
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;
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
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;
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
;
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;
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.