Matching and then Calculating Average

AbuChowdhury — Thu, 10 Dec 2015 12:13:58 GMT

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

Re: Matching and then Calculating Average

ballardw — Thu, 10 Dec 2015 16:47:12 GMT

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;

topic Re: Matching and then Calculating Average in SAS Programming

Matching and then Calculating Average

Re: Matching and then Calculating Average