Repeated times of each category

[mspak]

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

[PGStats · Accepted solution]

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

[mspak]

Thank you PG,

It is the correct answer

Regards,

mspak