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

Good days to all,

I have a SAS file with the following variables:

hk_id = identification code for each IPO (Initial Public Offering) firm

Industry = Industry for each firms

underwriters_id = identification code for underwriters involved

underwriters = Classified as either "MAIN UNDERWRITER" or "CO-UNDERWRITER"

I wish to calculate the following variable:

Number of IPOs in the same industry that the underwriters involved in the dataset = same_ind


My expected output:

hk_id

Industry

underwriters_id

industry

same_ind

av_same_ind


For example, the IPO firm with hk_id=2, Industry =Basic Materials; 3 underwriters involved ib002, ib003 and ib005. I would wish to know how many times each of the underwriter (ib002, ib003 and ib005) handled IPOs (based on the sample given)Basic Materials" industry (same_ind). Then, I will output the average times in which all the underwriters for each firm handled the IPOs in the same industry (av_same_ind). av_same_ind is calculated as sum of same_ind of all underwriters for each IPO firm/number of underwriters involved in the IPO. 


Thank you in advance for any advices.


Regards,

mspak

 


1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

If I understood your request correctly :

proc sql;

create table uw_ind_rpt as

select hk_id, industry, underwriters_id, count(hk_id) as same_ind

from sasforum.uw_ind

group by industry, underwriters_id;

create table uw_ind_av as

select hk_id, industry, mean(same_ind) as av_same_ind

from uw_ind_rpt

group by hk_id, industry;

PG

PG

View solution in original post

2 REPLIES 2
PGStats
Opal | Level 21

If I understood your request correctly :

proc sql;

create table uw_ind_rpt as

select hk_id, industry, underwriters_id, count(hk_id) as same_ind

from sasforum.uw_ind

group by industry, underwriters_id;

create table uw_ind_av as

select hk_id, industry, mean(same_ind) as av_same_ind

from uw_ind_rpt

group by hk_id, industry;

PG

PG
mspak
Quartz | Level 8

Thank you PG,

It is the correct answer Smiley Happy

Regards,

mspak

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
  • 2 replies
  • 655 views
  • 0 likes
  • 2 in conversation