## remove recurring prices

Solved
Occasional Contributor
Posts: 15

# remove recurring prices

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-04-2017 03:15 AM
Posts: 1,337

## Re: remove recurring prices

If, for each ID group, you had the value of ZERO_START, i.e. the within-id-group record position starting the closing series of RET=0 then you could

data want;

do N=1 by 1 until (last.id);

set have;

by id;

if N>zerostart then ret=.;

output;

end;

run;

The program below precedes the above code with another DO UNTIL (last.xxx) loop to determine the value of zerostart:

``````data want (drop=N zero_start);
do n=1 by 1 until (last.id);
set have;
by id ret notsorted;
if first.ret then zero_start=ifn(ret=0,n,.);
end;

do n=1 by 1 until (last.id);
set have;
by id;
if n>=zero_start then ret=.;
output;
end;
run;

``````

Notes:

1. The first DO UNTIL loop has two BY vars - ID and RET, and uses the NOTSORTED option so that it need not have RET in ascending order.
2. ZEROSTART will be updated at the start of each incoming RET sub-group, so it ends up depending only on the value of the most recent RET sub-group within the ID.  It will be a record position counter (N) if the sub-group had ret=0, and will be missing otherwise.  This allows easy testing in the second DO UNTIL group.
3. Of course, this does not provide a way to detect true zero returns at end of the id vs zero returns artificially derived from delisted stocks.  Don't you have delisting or censoring dates for each ID somewhere?  That would be a much better resource to solve this problem.

All Replies
Super User
Posts: 13,498

## Re: remove recurring prices

It would be best to show the code generating the data set and a small example data set of the input data as well as the desired output for that input data. The input data should include at least one case with the issue and one without.

Best is to post data in the form of a data step as otherwise we have to guess about data types, lengths, types and such.

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.

Solution
‎08-04-2017 03:15 AM
Posts: 1,337

## Re: remove recurring prices

If, for each ID group, you had the value of ZERO_START, i.e. the within-id-group record position starting the closing series of RET=0 then you could

data want;

do N=1 by 1 until (last.id);

set have;

by id;

if N>zerostart then ret=.;

output;

end;

run;

The program below precedes the above code with another DO UNTIL (last.xxx) loop to determine the value of zerostart:

``````data want (drop=N zero_start);
do n=1 by 1 until (last.id);
set have;
by id ret notsorted;
if first.ret then zero_start=ifn(ret=0,n,.);
end;

do n=1 by 1 until (last.id);
set have;
by id;
if n>=zero_start then ret=.;
output;
end;
run;

``````

Notes:

1. The first DO UNTIL loop has two BY vars - ID and RET, and uses the NOTSORTED option so that it need not have RET in ascending order.
2. ZEROSTART will be updated at the start of each incoming RET sub-group, so it ends up depending only on the value of the most recent RET sub-group within the ID.  It will be a record position counter (N) if the sub-group had ret=0, and will be missing otherwise.  This allows easy testing in the second DO UNTIL group.
3. Of course, this does not provide a way to detect true zero returns at end of the id vs zero returns artificially derived from delisted stocks.  Don't you have delisting or censoring dates for each ID somewhere?  That would be a much better resource to solve this problem.
Occasional Contributor
Posts: 15

## Re: remove recurring prices

Thank youu again

☑ This topic is solved.