Help using Base SAS procedures

Repeated times of each category

Accepted Solution Solved
Reply
Regular Contributor
Posts: 162
Accepted Solution

Repeated times of each category

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

 


Attachment

Accepted Solutions
Solution
‎04-29-2012 09:21 PM
Respected Advisor
Posts: 4,926

Re: Repeated times of each category

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


All Replies
Solution
‎04-29-2012 09:21 PM
Respected Advisor
Posts: 4,926

Re: Repeated times of each category

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
Regular Contributor
Posts: 162

Re: Repeated times of each category

Thank you PG,

It is the correct answer Smiley Happy

Regards,

mspak

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 149 views
  • 0 likes
  • 2 in conversation