SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Problem with recurring prices

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

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.

 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 :

idpricereturndate
117.68753031-Jan-00
123.7529.4724429-Feb-00
116.6875-35.292331-Mar-00
115.0625-10.245230-Apr-00
113.875-8.2119631-May-00
112-14.518230-Jun-00
112031-Jul-00
111.25-6.4538531-Aug-00
111.43751.6529330-Sep-00
111.93754.27872831-Oct-00
110.375-14.028630-Nov-00
112.62519.6279931-Dec-00
115.1918.4958331-Jan-01
113.6-11.056828-Feb-01
112.76-6.3754531-Mar-01
111.23-12.772730-Apr-01
11422.0468631-May-01
117.120.0021130-Jun-01
116.52-3.4506731-Jul-01
117.063.21647731-Aug-01
18.01-75.604630-Sep-01
17.6-5.2542531-Oct-01
18.156.98696830-Nov-01
19.0110.0317131-Dec-01
18.6-4.6572931-Jan-02
17.4-15.028228-Feb-02
110.7737.5284531-Mar-02
112.7416.7982230-Apr-02
111.44-10.763131-May-02
110.2-11.472830-Jun-02
18.74-15.447831-Jul-02
16.2-34.336131-Aug-02
14.75-26.640530-Sep-02
13.91-19.460731-Oct-02
15.534.1210730-Nov-02
15-9.5310231-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
218.015131-Jan-00
217.77-1.3693129-Feb-00
219.559.54636431-Mar-00
218.865-3.5666930-Apr-00
220.096.29138331-May-00
222.4711.1958930-Jun-00
221.565-4.1109531-Jul-00
222.9856.3770231-Aug-00
220.11-13.362530-Sep-00
220.843.56570131-Oct-00
220.245-2.8966430-Nov-00
221.284.98598131-Dec-00
221.340.28155831-Jan-01
222.374.71375328-Feb-01
223.2553.87993931-Mar-01
222-5.5477730-Apr-01
222031-May-01
222030-Jun-01
225.36514.2327831-Jul-01
227.4757.99062331-Aug-01
230.1159.17468930-Sep-01
225.19-17.857631-Oct-01
226.2854.25513430-Nov-01
231.3917.7490931-Dec-01
229.815-5.1477731-Jan-02
234.8815.690228-Feb-02
237.166.33193131-Mar-02
235.6-4.2887330-Apr-02
239.55510.5346531-May-02
244.66512.1498130-Jun-02
243.895-1.7389831-Jul-02
241.88-4.699231-Aug-02
239.03-7.0477830-Sep-02
235.05-10.755531-Oct-02
237.637.10259930-Nov-02
239.063.72973231-Dec-02
244.7113.5098331-Jan-03
256.6923.7400628-Feb-03
252.2-8.2515331-Mar-03
254.163.68601330-Apr-03
253.12-1.9389131-May-03
254.612.76635130-Jun-03
253.46-2.1283331-Jul-03
254.511.94504631-Aug-03
246.34-16.237930-Sep-03
250.478.53735831-Oct-03
253.385.60570430-Nov-03
251.91-2.7924731-Dec-03
250.76-2.2402831-Jan-04
244.5-13.161929-Feb-04
244.32-0.4053131-Mar-04
241.9-5.6150230-Apr-04
245.949.20503731-May-04
238.54-17.56430-Jun-04
238.650.28501131-Jul-04
240.75.16813231-Aug-04
244.1758.19309330-Sep-04
2451.85034731-Oct-04
2486.45385230-Nov-04
2504.08219931-Dec-04

Accepted Solutions
Solution
‎08-05-2017 05:36 AM
Respected Advisor
Posts: 4,173

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;

 

View solution in original post


All Replies
Super User
Posts: 5,441

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
Respected Advisor
Posts: 4,173

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.

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

Discussion stats
  • 2 replies
  • 258 views
  • 0 likes
  • 3 in conversation