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
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.