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

[kwil]

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.

[s_lassen · Accepted solution]

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.

[kwil]

Thanks very much!