cumulative product of a variable by date

[jkim197]

Hello SAS users

I'm now currently working with shares outstanding variable with panel data of 2800 stocks.

I want to calculate adjusted common share outstanding, hence I need cumulative adjustment factor.

However, I am struggling with this.

What I want to do is described below.

I got this kind of data structure. with daily frequency

stock  date  adjustment_factor

1        31/01/2000       1

1        01/02/2000       1

1        02/02/2000       1.2

1        10/02/2000        1

1        11/02/2000        1

1        12/02/2000        1

2        31/01/2000        1

2        01/02/2000        1

2        02/02/2000        1

2        10/02/2000         2.2

2        11/02/2000         1

2        12/02/2000         1.7

2         13/02/2000        1

So that I want to genereate cumulative_adjustment_factor

stock  date  adjustment_factor        cumulative_adjustment_factor

1        31/01/2000       1                          1

1        01/02/2000       1                          1

1        02/02/2000       1.2                        1.2

1        10/02/2000        1                          1.2

1        11/02/2000        1                           1.2

1        12/02/2000        1                          1.2

2        31/01/2000        1                           1

2        01/02/2000        1                           1

2        02/02/2000        1                           1

2        10/02/2000         2.2                       2.2

2        11/02/2000         1                          2.2

2        12/02/2000         1.7                       2.2*1.7=3.74

2         13/02/2000        1                          3.74

notice that there are gaps with date because non-business days are absent.

Any idea for doing this?

[ballardw · Accepted solution]

This may get you started:

data have;
   informat  stock $5.  date ddmmyy10. adjustment_factor best8.;
   format date ddmmyy10.;
   input stock  date  adjustment_factor;
datalines;
1        31/01/2000       1
1        01/02/2000       1
1        02/02/2000       1.2
1        10/02/2000        1
1        11/02/2000        1
1        12/02/2000        1
2        31/01/2000        1
2        01/02/2000        1
2        02/02/2000        1
2        10/02/2000         2.2
2        11/02/2000         1
2        12/02/2000         1.7
2         13/02/2000        1
;

data want;
   set have;
   by stock date;
   retain cum_adjust;
   if first.stock then cum_adjust = adjustment_factor;
   else cum_adjust = cum_adjust*adjustment_factor ;
run;

Please post data in the form of a data step such as I used for the data have. It is possible that your variable types may not work for what we assume when you do not provide such and a suggested solution wouldn't work.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

[jkim197]

Thank You Mein Grand Advisor.

It really worked well.