## Problem with recurring prices

Solved
Occasional Contributor
Posts: 15

# Problem with recurring prices

I have a dataset as following :

For each stock id at each date, I have its relative price for which I have calculated its return. The problem is that my data give recurring prices for the stocks that became inactive after a certain date. I want to remove the recurring prices or to replace these prices by a ‘.’

Thus, I want to check for each id, the prices that are at the end ( to see if I have recurring prices and if so to remove them or to replace them with a dot)

Since I have recurring prices, then their relative returns will be zeros. However I cannot delete all returns that are zeros since sometimes I have recurring prices that are not due to the fact that the stock became inactive, for example for id=2 for the period 31 may 2001-31july 2001 .

I think the solution would be to start at the end of each period for each stock, by seeing if Rt=0 or if I have recurring price  and go up until I have Rt that is different from zero or a different price but I don t know how to do that

Your help is much appreciated because I have a  huge dataset.

 id price return date 1 17.6875 30 31-Jan-00 1 23.75 29.47244 29-Feb-00 1 16.6875 -35.2923 31-Mar-00 1 15.0625 -10.2452 30-Apr-00 1 13.875 -8.21196 31-May-00 1 12 -14.5182 30-Jun-00 1 12 0 31-Jul-00 1 11.25 -6.45385 31-Aug-00 1 11.4375 1.65293 30-Sep-00 1 11.9375 4.278728 31-Oct-00 1 10.375 -14.0286 30-Nov-00 1 12.625 19.62799 31-Dec-00 1 15.19 18.49583 31-Jan-01 1 13.6 -11.0568 28-Feb-01 1 12.76 -6.37545 31-Mar-01 1 11.23 -12.7727 30-Apr-01 1 14 22.04686 31-May-01 1 17.1 20.00211 30-Jun-01 1 16.52 -3.45067 31-Jul-01 1 17.06 3.216477 31-Aug-01 1 8.01 -75.6046 30-Sep-01 1 7.6 -5.25425 31-Oct-01 1 8.15 6.986968 30-Nov-01 1 9.01 10.03171 31-Dec-01 1 8.6 -4.65729 31-Jan-02 1 7.4 -15.0282 28-Feb-02 1 10.77 37.52845 31-Mar-02 1 12.74 16.79822 30-Apr-02 1 11.44 -10.7631 31-May-02 1 10.2 -11.4728 30-Jun-02 1 8.74 -15.4478 31-Jul-02 1 6.2 -34.3361 31-Aug-02 1 4.75 -26.6405 30-Sep-02 1 3.91 -19.4607 31-Oct-02 1 5.5 34.12107 30-Nov-02 1 5 -9.53102 31-Dec-02 1 5 0 31-Jan-03 1 5 0 28-Feb-03 1 5 0 31-Mar-03 1 5 0 30-Apr-03 1 5 0 31-May-03 1 5 0 30-Jun-03 1 5 0 31-Jul-03 1 5 0 31-Aug-03 1 5 0 30-Sep-03 1 5 0 31-Oct-03 1 5 0 30-Nov-03 1 5 0 31-Dec-03 1 5 0 31-Jan-04 1 5 0 29-Feb-04 1 5 0 31-Mar-04 1 5 0 30-Apr-04 1 5 0 31-May-04 1 5 0 30-Jun-04 1 5 0 31-Jul-04 1 5 0 31-Aug-04 1 5 0 30-Sep-04 1 5 0 31-Oct-04 1 5 0 30-Nov-04 1 5 0 31-Dec-04 2 18.015 1 31-Jan-00 2 17.77 -1.36931 29-Feb-00 2 19.55 9.546364 31-Mar-00 2 18.865 -3.56669 30-Apr-00 2 20.09 6.291383 31-May-00 2 22.47 11.19589 30-Jun-00 2 21.565 -4.11095 31-Jul-00 2 22.985 6.37702 31-Aug-00 2 20.11 -13.3625 30-Sep-00 2 20.84 3.565701 31-Oct-00 2 20.245 -2.89664 30-Nov-00 2 21.28 4.985981 31-Dec-00 2 21.34 0.281558 31-Jan-01 2 22.37 4.713753 28-Feb-01 2 23.255 3.879939 31-Mar-01 2 22 -5.54777 30-Apr-01 2 22 0 31-May-01 2 22 0 30-Jun-01 2 22 0 31-Jul-01 2 27.475 7.990623 31-Aug-01 2 30.115 9.174689 30-Sep-01 2 25.19 -17.8576 31-Oct-01 2 26.285 4.255134 30-Nov-01 2 31.39 17.74909 31-Dec-01 2 29.815 -5.14777 31-Jan-02 2 34.88 15.6902 28-Feb-02 2 37.16 6.331931 31-Mar-02 2 35.6 -4.28873 30-Apr-02 2 39.555 10.53465 31-May-02 2 44.665 12.14981 30-Jun-02 2 43.895 -1.73898 31-Jul-02 2 41.88 -4.6992 31-Aug-02 2 39.03 -7.04778 30-Sep-02 2 35.05 -10.7555 31-Oct-02 2 37.63 7.102599 30-Nov-02 2 39.06 3.729732 31-Dec-02 2 44.71 13.50983 31-Jan-03 2 56.69 23.74006 28-Feb-03 2 52.2 -8.25153 31-Mar-03 2 54.16 3.686013 30-Apr-03 2 53.12 -1.93891 31-May-03 2 54.61 2.766351 30-Jun-03 2 53.46 -2.12833 31-Jul-03 2 54.51 1.945046 31-Aug-03 2 46.34 -16.2379 30-Sep-03 2 50.47 8.537358 31-Oct-03 2 53.38 5.605704 30-Nov-03 2 51.91 -2.79247 31-Dec-03 2 50.76 -2.24028 31-Jan-04 2 44.5 -13.1619 29-Feb-04 2 44.32 -0.40531 31-Mar-04 2 41.9 -5.61502 30-Apr-04 2 45.94 9.205037 31-May-04 2 38.54 -17.564 30-Jun-04 2 38.65 0.285011 31-Jul-04 2 40.7 5.168132 31-Aug-04 2 44.175 8.193093 30-Sep-04 2 45 1.850347 31-Oct-04 2 48 6.453852 30-Nov-04 2 50 4.082199 31-Dec-04

what I would like to have :

 id price return date 1 17.6875 30 31-Jan-00 1 23.75 29.47244 29-Feb-00 1 16.6875 -35.2923 31-Mar-00 1 15.0625 -10.2452 30-Apr-00 1 13.875 -8.21196 31-May-00 1 12 -14.5182 30-Jun-00 1 12 0 31-Jul-00 1 11.25 -6.45385 31-Aug-00 1 11.4375 1.65293 30-Sep-00 1 11.9375 4.278728 31-Oct-00 1 10.375 -14.0286 30-Nov-00 1 12.625 19.62799 31-Dec-00 1 15.19 18.49583 31-Jan-01 1 13.6 -11.0568 28-Feb-01 1 12.76 -6.37545 31-Mar-01 1 11.23 -12.7727 30-Apr-01 1 14 22.04686 31-May-01 1 17.1 20.00211 30-Jun-01 1 16.52 -3.45067 31-Jul-01 1 17.06 3.216477 31-Aug-01 1 8.01 -75.6046 30-Sep-01 1 7.6 -5.25425 31-Oct-01 1 8.15 6.986968 30-Nov-01 1 9.01 10.03171 31-Dec-01 1 8.6 -4.65729 31-Jan-02 1 7.4 -15.0282 28-Feb-02 1 10.77 37.52845 31-Mar-02 1 12.74 16.79822 30-Apr-02 1 11.44 -10.7631 31-May-02 1 10.2 -11.4728 30-Jun-02 1 8.74 -15.4478 31-Jul-02 1 6.2 -34.3361 31-Aug-02 1 4.75 -26.6405 30-Sep-02 1 3.91 -19.4607 31-Oct-02 1 5.5 34.12107 30-Nov-02 1 5 -9.53102 31-Dec-02 1 . . 31-Jan-03 1 . . 28-Feb-03 1 . . 31-Mar-03 1 . . 30-Apr-03 1 . . 31-May-03 1 . . 30-Jun-03 1 . . 31-Jul-03 1 . . 31-Aug-03 1 . . 30-Sep-03 1 . . 31-Oct-03 1 . . 30-Nov-03 1 . . 31-Dec-03 1 . . 31-Jan-04 1 . . 29-Feb-04 1 . . 31-Mar-04 1 . . 30-Apr-04 1 . . 31-May-04 1 . . 30-Jun-04 1 . . 31-Jul-04 1 . . 31-Aug-04 1 . . 30-Sep-04 1 . . 31-Oct-04 1 . . 30-Nov-04 1 . . 31-Dec-04 2 18.015 1 31-Jan-00 2 17.77 -1.36931 29-Feb-00 2 19.55 9.546364 31-Mar-00 2 18.865 -3.56669 30-Apr-00 2 20.09 6.291383 31-May-00 2 22.47 11.19589 30-Jun-00 2 21.565 -4.11095 31-Jul-00 2 22.985 6.37702 31-Aug-00 2 20.11 -13.3625 30-Sep-00 2 20.84 3.565701 31-Oct-00 2 20.245 -2.89664 30-Nov-00 2 21.28 4.985981 31-Dec-00 2 21.34 0.281558 31-Jan-01 2 22.37 4.713753 28-Feb-01 2 23.255 3.879939 31-Mar-01 2 22 -5.54777 30-Apr-01 2 22 0 31-May-01 2 22 0 30-Jun-01 2 25.365 14.23278 31-Jul-01 2 27.475 7.990623 31-Aug-01 2 30.115 9.174689 30-Sep-01 2 25.19 -17.8576 31-Oct-01 2 26.285 4.255134 30-Nov-01 2 31.39 17.74909 31-Dec-01 2 29.815 -5.14777 31-Jan-02 2 34.88 15.6902 28-Feb-02 2 37.16 6.331931 31-Mar-02 2 35.6 -4.28873 30-Apr-02 2 39.555 10.53465 31-May-02 2 44.665 12.14981 30-Jun-02 2 43.895 -1.73898 31-Jul-02 2 41.88 -4.6992 31-Aug-02 2 39.03 -7.04778 30-Sep-02 2 35.05 -10.7555 31-Oct-02 2 37.63 7.102599 30-Nov-02 2 39.06 3.729732 31-Dec-02 2 44.71 13.50983 31-Jan-03 2 56.69 23.74006 28-Feb-03 2 52.2 -8.25153 31-Mar-03 2 54.16 3.686013 30-Apr-03 2 53.12 -1.93891 31-May-03 2 54.61 2.766351 30-Jun-03 2 53.46 -2.12833 31-Jul-03 2 54.51 1.945046 31-Aug-03 2 46.34 -16.2379 30-Sep-03 2 50.47 8.537358 31-Oct-03 2 53.38 5.605704 30-Nov-03 2 51.91 -2.79247 31-Dec-03 2 50.76 -2.24028 31-Jan-04 2 44.5 -13.1619 29-Feb-04 2 44.32 -0.40531 31-Mar-04 2 41.9 -5.61502 30-Apr-04 2 45.94 9.205037 31-May-04 2 38.54 -17.564 30-Jun-04 2 38.65 0.285011 31-Jul-04 2 40.7 5.168132 31-Aug-04 2 44.175 8.193093 30-Sep-04 2 45 1.850347 31-Oct-04 2 48 6.453852 30-Nov-04 2 50 4.082199 31-Dec-04

Accepted Solutions
Solution
‎08-05-2017 05:36 AM
Posts: 4,366

## Re: Problem with recurring prices

[ Edited ]

@bera00

Assuming your data is already sorted by ID and DATE below code should do what you're after without the need to resort your data.

``````data want(drop=_:);

/* determine the first inactive date; missing if none */
call missing(_inactive_date);
format _inactive_date date9.;
do until(last.id);
set have(keep=id date return);
by id date;
if return ne 0 then call missing(_inactive_date);
else if missing(_inactive_date) then _inactive_date=date;
end;

/* set return to missing for all dates >= inactive date */
do until(last.id);
set have;
by id date;
if . < _inactive_date <= date then call missing(return);
output;
end;
run;``````

Appreciate that you've posted sample data. It would be really helpful though if you post such data in the future via a SAS data step so we get something from you which we can directly use without extra effort on our end.

Below what I'm talking about - either posted into a code window as below or as a text attachment.

``````data have;
infile datalines dsd truncover;
input id price return date:anydtdte.;
format date date9.;
datalines;
1,17.6875,30,31-Jan-00
1,23.75,29.47244,29-Feb-00
1,16.6875,-35.2923,31-Mar-00
1,15.0625,-10.2452,30-Apr-00
1,13.875,-8.21196,31-May-00
1,12,-14.5182,30-Jun-00
1,12,0,31-Jul-00
1,11.25,-6.45385,31-Aug-00
1,11.4375,1.65293,30-Sep-00
1,11.9375,4.278728,31-Oct-00
1,10.375,-14.0286,30-Nov-00
1,12.625,19.62799,31-Dec-00
1,15.19,18.49583,31-Jan-01
1,13.6,-11.0568,28-Feb-01
1,12.76,-6.37545,31-Mar-01
1,11.23,-12.7727,30-Apr-01
1,14,22.04686,31-May-01
1,17.1,20.00211,30-Jun-01
1,16.52,-3.45067,31-Jul-01
1,17.06,3.216477,31-Aug-01
1,8.01,-75.6046,30-Sep-01
1,7.6,-5.25425,31-Oct-01
1,8.15,6.986968,30-Nov-01
1,9.01,10.03171,31-Dec-01
1,8.6,-4.65729,31-Jan-02
1,7.4,-15.0282,28-Feb-02
1,10.77,37.52845,31-Mar-02
1,12.74,16.79822,30-Apr-02
1,11.44,-10.7631,31-May-02
1,10.2,-11.4728,30-Jun-02
1,8.74,-15.4478,31-Jul-02
1,6.2,-34.3361,31-Aug-02
1,4.75,-26.6405,30-Sep-02
1,3.91,-19.4607,31-Oct-02
1,5.5,34.12107,30-Nov-02
1,5,-9.53102,31-Dec-02
1,5,0,31-Jan-03
1,5,0,28-Feb-03
1,5,0,31-Mar-03
1,5,0,30-Apr-03
1,5,0,31-May-03
1,5,0,30-Jun-03
1,5,0,31-Jul-03
1,5,0,31-Aug-03
1,5,0,30-Sep-03
1,5,0,31-Oct-03
1,5,0,30-Nov-03
1,5,0,31-Dec-03
1,5,0,31-Jan-04
1,5,0,29-Feb-04
1,5,0,31-Mar-04
1,5,0,30-Apr-04
1,5,0,31-May-04
1,5,0,30-Jun-04
1,5,0,31-Jul-04
1,5,0,31-Aug-04
1,5,0,30-Sep-04
1,5,0,31-Oct-04
1,5,0,30-Nov-04
1,5,0,31-Dec-04
2,18.015,1,31-Jan-00
2,17.77,-1.36931,29-Feb-00
2,19.55,9.546364,31-Mar-00
2,18.865,-3.56669,30-Apr-00
2,20.09,6.291383,31-May-00
2,22.47,11.19589,30-Jun-00
2,21.565,-4.11095,31-Jul-00
2,22.985,6.37702,31-Aug-00
2,20.11,-13.3625,30-Sep-00
2,20.84,3.565701,31-Oct-00
2,20.245,-2.89664,30-Nov-00
2,21.28,4.985981,31-Dec-00
2,21.34,0.281558,31-Jan-01
2,22.37,4.713753,28-Feb-01
2,23.255,3.879939,31-Mar-01
2,22,-5.54777,30-Apr-01
2,22,0,31-May-01
2,22,0,30-Jun-01
2,22,0,31-Jul-01
2,27.475,7.990623,31-Aug-01
2,30.115,9.174689,30-Sep-01
2,25.19,-17.8576,31-Oct-01
2,26.285,4.255134,30-Nov-01
2,31.39,17.74909,31-Dec-01
2,29.815,-5.14777,31-Jan-02
2,34.88,15.6902,28-Feb-02
2,37.16,6.331931,31-Mar-02
2,35.6,-4.28873,30-Apr-02
2,39.555,10.53465,31-May-02
2,44.665,12.14981,30-Jun-02
2,43.895,-1.73898,31-Jul-02
2,41.88,-4.6992,31-Aug-02
2,39.03,-7.04778,30-Sep-02
2,35.05,-10.7555,31-Oct-02
2,37.63,7.102599,30-Nov-02
2,39.06,3.729732,31-Dec-02
2,44.71,13.50983,31-Jan-03
2,56.69,23.74006,28-Feb-03
2,52.2,-8.25153,31-Mar-03
2,54.16,3.686013,30-Apr-03
2,53.12,-1.93891,31-May-03
2,54.61,2.766351,30-Jun-03
2,53.46,-2.12833,31-Jul-03
2,54.51,1.945046,31-Aug-03
2,46.34,-16.2379,30-Sep-03
2,50.47,8.537358,31-Oct-03
2,53.38,5.605704,30-Nov-03
2,51.91,-2.79247,31-Dec-03
2,50.76,-2.24028,31-Jan-04
2,44.5,-13.1619,29-Feb-04
2,44.32,-0.40531,31-Mar-04
2,41.9,-5.61502,30-Apr-04
2,45.94,9.205037,31-May-04
2,38.54,-17.564,30-Jun-04
2,38.65,0.285011,31-Jul-04
2,40.7,5.168132,31-Aug-04
2,44.175,8.193093,30-Sep-04
2,45,1.850347,31-Oct-04
2,48,6.453852,30-Nov-04
2,50,4.082199,31-Dec-04
;
run;
``````

All Replies
Super User
Posts: 5,687

## Re: Problem with recurring prices

It sounds that your approach could be feasable.
As you might understand you need to resort your data descending by date, and use RETAIN OR LAGn to keep track of the values from the previous observation.
Data never sleeps
Solution
‎08-05-2017 05:36 AM
Posts: 4,366

## Re: Problem with recurring prices

[ Edited ]

@bera00

Assuming your data is already sorted by ID and DATE below code should do what you're after without the need to resort your data.

``````data want(drop=_:);

/* determine the first inactive date; missing if none */
call missing(_inactive_date);
format _inactive_date date9.;
do until(last.id);
set have(keep=id date return);
by id date;
if return ne 0 then call missing(_inactive_date);
else if missing(_inactive_date) then _inactive_date=date;
end;

/* set return to missing for all dates >= inactive date */
do until(last.id);
set have;
by id date;
if . < _inactive_date <= date then call missing(return);
output;
end;
run;``````

Appreciate that you've posted sample data. It would be really helpful though if you post such data in the future via a SAS data step so we get something from you which we can directly use without extra effort on our end.

Below what I'm talking about - either posted into a code window as below or as a text attachment.

``````data have;
infile datalines dsd truncover;
input id price return date:anydtdte.;
format date date9.;
datalines;
1,17.6875,30,31-Jan-00
1,23.75,29.47244,29-Feb-00
1,16.6875,-35.2923,31-Mar-00
1,15.0625,-10.2452,30-Apr-00
1,13.875,-8.21196,31-May-00
1,12,-14.5182,30-Jun-00
1,12,0,31-Jul-00
1,11.25,-6.45385,31-Aug-00
1,11.4375,1.65293,30-Sep-00
1,11.9375,4.278728,31-Oct-00
1,10.375,-14.0286,30-Nov-00
1,12.625,19.62799,31-Dec-00
1,15.19,18.49583,31-Jan-01
1,13.6,-11.0568,28-Feb-01
1,12.76,-6.37545,31-Mar-01
1,11.23,-12.7727,30-Apr-01
1,14,22.04686,31-May-01
1,17.1,20.00211,30-Jun-01
1,16.52,-3.45067,31-Jul-01
1,17.06,3.216477,31-Aug-01
1,8.01,-75.6046,30-Sep-01
1,7.6,-5.25425,31-Oct-01
1,8.15,6.986968,30-Nov-01
1,9.01,10.03171,31-Dec-01
1,8.6,-4.65729,31-Jan-02
1,7.4,-15.0282,28-Feb-02
1,10.77,37.52845,31-Mar-02
1,12.74,16.79822,30-Apr-02
1,11.44,-10.7631,31-May-02
1,10.2,-11.4728,30-Jun-02
1,8.74,-15.4478,31-Jul-02
1,6.2,-34.3361,31-Aug-02
1,4.75,-26.6405,30-Sep-02
1,3.91,-19.4607,31-Oct-02
1,5.5,34.12107,30-Nov-02
1,5,-9.53102,31-Dec-02
1,5,0,31-Jan-03
1,5,0,28-Feb-03
1,5,0,31-Mar-03
1,5,0,30-Apr-03
1,5,0,31-May-03
1,5,0,30-Jun-03
1,5,0,31-Jul-03
1,5,0,31-Aug-03
1,5,0,30-Sep-03
1,5,0,31-Oct-03
1,5,0,30-Nov-03
1,5,0,31-Dec-03
1,5,0,31-Jan-04
1,5,0,29-Feb-04
1,5,0,31-Mar-04
1,5,0,30-Apr-04
1,5,0,31-May-04
1,5,0,30-Jun-04
1,5,0,31-Jul-04
1,5,0,31-Aug-04
1,5,0,30-Sep-04
1,5,0,31-Oct-04
1,5,0,30-Nov-04
1,5,0,31-Dec-04
2,18.015,1,31-Jan-00
2,17.77,-1.36931,29-Feb-00
2,19.55,9.546364,31-Mar-00
2,18.865,-3.56669,30-Apr-00
2,20.09,6.291383,31-May-00
2,22.47,11.19589,30-Jun-00
2,21.565,-4.11095,31-Jul-00
2,22.985,6.37702,31-Aug-00
2,20.11,-13.3625,30-Sep-00
2,20.84,3.565701,31-Oct-00
2,20.245,-2.89664,30-Nov-00
2,21.28,4.985981,31-Dec-00
2,21.34,0.281558,31-Jan-01
2,22.37,4.713753,28-Feb-01
2,23.255,3.879939,31-Mar-01
2,22,-5.54777,30-Apr-01
2,22,0,31-May-01
2,22,0,30-Jun-01
2,22,0,31-Jul-01
2,27.475,7.990623,31-Aug-01
2,30.115,9.174689,30-Sep-01
2,25.19,-17.8576,31-Oct-01
2,26.285,4.255134,30-Nov-01
2,31.39,17.74909,31-Dec-01
2,29.815,-5.14777,31-Jan-02
2,34.88,15.6902,28-Feb-02
2,37.16,6.331931,31-Mar-02
2,35.6,-4.28873,30-Apr-02
2,39.555,10.53465,31-May-02
2,44.665,12.14981,30-Jun-02
2,43.895,-1.73898,31-Jul-02
2,41.88,-4.6992,31-Aug-02
2,39.03,-7.04778,30-Sep-02
2,35.05,-10.7555,31-Oct-02
2,37.63,7.102599,30-Nov-02
2,39.06,3.729732,31-Dec-02
2,44.71,13.50983,31-Jan-03
2,56.69,23.74006,28-Feb-03
2,52.2,-8.25153,31-Mar-03
2,54.16,3.686013,30-Apr-03
2,53.12,-1.93891,31-May-03
2,54.61,2.766351,30-Jun-03
2,53.46,-2.12833,31-Jul-03
2,54.51,1.945046,31-Aug-03
2,46.34,-16.2379,30-Sep-03
2,50.47,8.537358,31-Oct-03
2,53.38,5.605704,30-Nov-03
2,51.91,-2.79247,31-Dec-03
2,50.76,-2.24028,31-Jan-04
2,44.5,-13.1619,29-Feb-04
2,44.32,-0.40531,31-Mar-04
2,41.9,-5.61502,30-Apr-04
2,45.94,9.205037,31-May-04
2,38.54,-17.564,30-Jun-04
2,38.65,0.285011,31-Jul-04
2,40.7,5.168132,31-Aug-04
2,44.175,8.193093,30-Sep-04
2,45,1.850347,31-Oct-04
2,48,6.453852,30-Nov-04
2,50,4.082199,31-Dec-04
;
run;
``````

☑ This topic is solved.