topic Re: Problem with recurring prices in SAS Data Management

Problem with recurring prices

bera00 — Thu, 03 Aug 2017 16:25:32 GMT

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.

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

Re: Problem with recurring prices

LinusH — Thu, 03 Aug 2017 20:38:47 GMT

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.

Re: Problem with recurring prices

Patrick — Sat, 05 Aug 2017 11:42:59 GMT

@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;