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
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
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
Thank you PG,
It is the correct answer
Regards,
mspak
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.
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.