DATA Step, Macro, Functions and more

cumulative product of a variable by date

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

cumulative product of a variable by date

 

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?


Accepted Solutions
Solution
‎06-20-2017 04:17 AM
Super User
Posts: 11,343

Re: cumulative product of a variable by date

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.

View solution in original post


All Replies
Solution
‎06-20-2017 04:17 AM
Super User
Posts: 11,343

Re: cumulative product of a variable by date

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.

Occasional Contributor
Posts: 10

Re: cumulative product of a variable by date

Thank You Mein Grand Advisor.

 

It really worked well.

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 111 views
  • 2 likes
  • 2 in conversation