How to get a set of returns with replacement if data stops?

kwil — Tue, 22 Sep 2020 06:48:31 GMT

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.

Re: How to get a set of returns with replacement if data stops?

s_lassen — Tue, 22 Sep 2020 13:04:53 GMT

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.

Re: How to get a set of returns with replacement if data stops?

kwil — Wed, 23 Sep 2020 00:55:59 GMT

Thanks very much!

topic Re: How to get a set of returns with replacement if data stops? in SAS Programming

How to get a set of returns with replacement if data stops?

Re: How to get a set of returns with replacement if data stops?

Re: How to get a set of returns with replacement if data stops?