Estimate average values across groups each month

[Ruslan]

Hi All,

Could you please help me with the following issue?

I have companies each month with certain values like this:

Month      Company      Value

1                    1                10

1                    2                14

1                    5                15.5

1                    7                8

1                    9                3

1                   10               11

1                   13               16

1                   15               12

1                   16               19

1                   18               7

1                   20               11

1                   21               5

1                   22               8

2                   1                 9

2                   2                13

2                   3                20

2                   4                2

2                   5                19

2                   8                9

2                  10               10

2                  11               7

2                  12               13

2                  13               3

2                  15               20

2                  16               23

2                  18               17

2                  20               21

2                  21               8

I need to do the following. First, sort companies in ascending order into five groups (portfolios) each month (in total I have 203 months, the beginning of my sample is Jan1996) based on their value. Second, I need to go to Dec1996 i.e. 11 months after the beginning of the sample, get stocks in each of five groups (Low Value Group, 2, 3, 4, High Value Group) and then estimate the average of Value for each group (using the Value of those stocks that exist in month t) each month over the last 11 months (t-11) and over the next 11 months (t+11). Then, go to Jan1997, get stocks in each of five groups and estimate the average of Value for each group each month over the last 11 months and over the next 11 months. Next, do the same for the all other months so that I got rolling estimates. Finally, I need to estimate the time-series average of average Value for each group each month across all months. In other words, I need to get 23 observations for each group (from t-11 to t and from t to t+11 each month).

To summarize, each month I need to rank all companies in ascending order into five groups based on Value. The first group contains Low Value companies, the fifth group contains High Value companies. Then, I need to estimate average of Value for each of the five groups from eleven months before (t-11) until the eleven months after (t+11) group formation.    

You can simulate the months, companies, values like this: 

data have;
call streaminit(85765);
do month = 1 to 10;
    do Company =   1, 2, 5, 7, 9, 10, 13, 15, 16, 18, 20, 21, 22;
        value = round(rand("UNIFORM") * 25, 0.5);
        output;
        end;
    end;
run;

Any help will be hugely appreciated!!! Please, please help me with this issue!!!

[Ruslan]

Guys, please, I urgently need some help

[RW9]

Hi,

I am not seeing what the question is?  Post a question, with some test data and required output.

[Ruslan]

Hi,

This is the data that I have: (Alternatively you can look at my post) 

data have;
call streaminit(85765);
do month = 1 to 10;
    do Company =   1, 2, 5, 7, 9, 10, 13, 15, 16, 18, 20, 21, 22;
        value = round(rand("UNIFORM") * 25, 0.5);
        output;
        end;
    end;
run;

This is what I need to do: (Alternatively, for a detailed explanation you can look at the post)

To summarize, each month I need to rank all companies in ascending order into five groups based on Value. The first group contains Low Value companies, the fifth group contains High Value companies. Then, I need to estimate average of Value for each of the five groups from eleven months before (t-11) until the eleven months after (t+11) group formation. 

This is what I need to get: (It is hard to show an exact output)

I need to get 23 observations for each group (from t-11 to t and from t to t+11 each month)

Any help would be hugely appreciated!!!

[Ruslan]

Guys, please help me with this issue

[RW9]

Again, I am not seeing a question here.  You have mentioned all the procedures you need to do what you want - sort, rank, means etc.  What have you tried, what is not working.  And example of getting previous 11 months, and next 11 months could be like this:

data have;
  call streaminit(85765);
  do month=1 to 10;
    do Company=1, 2, 5, 7, 9, 10, 13, 15, 16, 18, 20, 21, 22;
      value = round(rand("UNIFORM") * 25, 0.5);
      output;
    end;
  end;
run;

proc sql;
  create table INTER as
  select  A.COMPANY,
          A.MONTH,
          (select sum(VALUE) from HAVE where COMPANY=A.COMPANY and MONTH between A.MONTH-11 and A.MONTH) as PREV_11,
          (select sum(VALUE) from HAVE where COMPANY=A.COMPANY and MONTH between A.MONTH and A.MONTH+11) as NEXT_11
  from    WORK.HAVE A
  order by COMPANY,MONTH;
quit;