Hi,
I have monthly returns for a set of firms that are ranked based on their closeness of match to the event firm. I want to create a set of returns for each event firm (there are multiple) based on the rank and month of the control firms. For example, since the first ranked company G has run out of data after month 3, i would like to use the total return data for company H from month 4 onwards until the data for company H runs out, at which point the next best ranked firm will be used if they have available data at that month and so on. There are 5 companies for each event and I would like to have a set of returns for each event firm up to month 60.
Have:
Event Company Rank Month Total Return
a G 1 1 0.05
a G 1 2 0.07
a G 1 3 0.09
a H 2 1 -0.05
a H 2 2 -0.01
a H 2 3 0.08
a H 2 4 0.06
a H 2 5 -0.01
Want:
Event Company Rank Month Total Return
a G 1 1 0.05
a G 1 2 0.07
a G 1 3 0.09
a H 2 4 0.06
a H 2 5 -0.01
a ... ... 60 -0.06
I would also like to know how I can do the same as above but also use the next best ranked firm if the current one has a total return of 0. For instance, since Company G in month 2 has a total return of 0, I'd like to use the total return of Company H (the next best ranked firm) before reverting back to G once the total return is no longer 0.
Have:
Event Company Rank Month Total Return
a G 1 1 0.05
a G 1 2 0
a G 1 3 0.09
a H 2 1 -0.05
a H 2 2 -0.01
a H 2 3 0.08
a H 2 4 0.06
a H 2 5 -0.01
Want:
Have:
Event Company Rank Month Total Return
a G 1 1 0.05
a H 2 2 -0.01
a G 1 3 0.09
a H 2 4 0.06
a H 2 5 -0.01
a ... ... 60 -0.06
Any help would be very much appreciated, still getting the hang of this!
Thanks.
That should not be so hard. This may work:
proc sort data <your input table>;
by event month rank;
run;
data want;
set <your input table>;
by event month;
if first.month;
run;
Your second request can be solved by changing the data step to this:
data want;
set <your input table>(where=(Total_return ne 0))
<your input table>(where=(Total_return=0));
by event month;
if first.month;
run;
I read the rows with zero return also, in case there are some months without a valid return. But with the WERE clause, they will be read last, and only used if the are no valid rows.
That should not be so hard. This may work:
proc sort data <your input table>;
by event month rank;
run;
data want;
set <your input table>;
by event month;
if first.month;
run;
Your second request can be solved by changing the data step to this:
data want;
set <your input table>(where=(Total_return ne 0))
<your input table>(where=(Total_return=0));
by event month;
if first.month;
run;
I read the rows with zero return also, in case there are some months without a valid return. But with the WERE clause, they will be read last, and only used if the are no valid rows.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: