BookmarkSubscribeRSS Feed
Ruslan
Calcite | Level 5

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!!!

 

 

5 REPLIES 5
Ruslan
Calcite | Level 5

Guys, please, I urgently need some help

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

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

Ruslan
Calcite | Level 5

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
Calcite | Level 5

Guys, please help me with this issue

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 606 views
  • 0 likes
  • 2 in conversation