BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bera00
Obsidian | Level 7

 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
1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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.
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

3 REPLIES 3
ballardw
Super User

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
PROC Star

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.
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
bera00
Obsidian | Level 7

 

Thank youu again

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 557 views
  • 1 like
  • 3 in conversation