12-10-2015 07:13 AM
My "Ipo" dataset looks the following:
And I attach another file "uw_rank". This file has the ranks of different manager in different years. I need to create a variable which will show the average rank of the managers in the "Ipo" dataset. For example, the first observation in the "Ipo" dataset (cusip: 00083310 and issue_year: 2006) has 3 managers: Credit Suisse, JP Morgan & Co Inc, and Bear Stearns & Co. Ranks of these 3 managers can be found in the "uw_rank" dataset. From this dataset, it can be seen that Credit Suisse does not have any rank (i.e. rank is blank), JP Morgan & Co cannot be found and Bear Stearns & Co has rank of 8.001 in 2006. So the average rank of this observation will be 8.001/1 = 8.001 (since only one manager has rank in this year 2006).
12-10-2015 11:45 AM - edited 12-10-2015 11:47 AM
It isn't clear from your question whether you have combined the data with your cuspid. If not it appears the only thing you have to match on is the name in the UW_RANK under manager_lead_name and the values there do not match how they appear in your lpo data set.
If you went to extra work to create the manager_lead_name with multiple companys in the lpo set I would suggest going back so that you have each company separately:
issue_date cuspid manager_lead_name issue_year
09/11/2006 00083310 Credit Suisse 2006
09/11/2006 00083310 JP Morgan & Co Inc 2006
09/11/2006 00083310 Bear Stearns and Co inc 2006
for example. That will be much easier to merge the rank data on (lpo refers to a set modified as above):
create table merged as
select lpo.issue_date, lpo.cuspid, lpo.manager_lead_name, lpo.issue_year, uw_rank.rank
from lpo join uw_rank
on lpo.manager_lead_name=uw_rank.manager_lead_name and lpo.issue_year = uw_rank.year
Then any of proc summary, means, report or tabulate can create the summaries.
proc means data=merged mean;
class manager_lead_name issue_year;