BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
comeon2012
Fluorite | Level 6

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.

dealnumcusip
139409104500001P
145559004500001P
145559204500001P
117501304500001Q
21545504500002A
21591204500002A
21603704500002A
23508904500002P
67151404500003F
69854004500003F
87630804500003F
94027204500003F
101261504500003F
104968804500003F
117765804500003F
192207604500003F
103210904500004Y
89302004500005H
133802404500005H
1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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;

View solution in original post

6 REPLIES 6
Jagadishkatam
Amethyst | Level 16

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
Haikuo
Onyx | Level 15

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;

Tom
Super User Tom
Super User

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

;

stat_sas
Ammonite | Level 13

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;

comeon2012
Fluorite | Level 6

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.

Jim_G
Pyrite | Level 9

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 3588 views
  • 4 likes
  • 6 in conversation