Matching and then Calculating Average

[AbuChowdhury]

Dear Experts,

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).

Thanks,

Abu

[ballardw]

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):

proc sql;

   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

   ;

quit;

Then any of proc summary, means, report or tabulate can create the summaries.

proc means data=merged mean;

   class manager_lead_name issue_year;

   var rank;

run;