remove recurring prices

[bera00]

 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.

 Thank you in advance

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

[ballardw]

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.

[mkeintz · Accepted solution]

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.

[bera00]

Thank youu again