topic Re: cumulative product of a variable by date in SAS Programming

cumulative product of a variable by date

jkim197 — Wed, 07 Jun 2017 05:23:20 GMT

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?

Re: cumulative product of a variable by date

ballardw — Wed, 07 Jun 2017 15:28:48 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: cumulative product of a variable by date

jkim197 — Tue, 20 Jun 2017 08:17:45 GMT

Thank You Mein Grand Advisor.

It really worked well.