52 week high - problem with stocks where high does not change

[merton76]

Hi All,

We have a problem with 52 week high data. We calculated 52 week highs - this is fine.

However we also want to calculate the number of days since the 52 week high.

The following code seemed to work fine and was provided from a forum member called null:

data dayssince;

   set 52weekhigh;

   by Ticker High NOTSORTED;

   if first.high then daysince=0;

   else daysince+1;

   run;

When applied to the following data we get:

Ticker	Price	High	days_since
1	0.09	0.09	0
1	0.08	0.09	1
1	0.09	0.09	2
1	0.09	0.09	3
1	0.07	0.09	4
1	0.05	0.09	5
1	0.09	0.09	6
1	0.08	0.09	7
1	0.07	0.09	8
1	0.06	0.09	9
1	0.08	0.09	10
1	0.09	0.09	11

The problem is that we want the clock to "reset" every time it reverts back to a new high, even though the high has not changed.

In other words, we need to start the clock from the last time the high was hit.

The result should look like this:

Ticker	Price	High	days_since
1	0.09	0.09	0
1	0.08	0.09	1
1	0.09	0.09	0
1	0.09	0.09	0
1	0.07	0.09	1
1	0.05	0.09	2
1	0.09	0.09	0
1	0.08	0.09	1
1	0.07	0.09	2
1	0.06	0.09	3
1	0.08	0.09	4
1	0.09	0.09	0

Any direction would be greatly assisted!

Many thanks

[Ksharp]

You mean John King ?  He is the best SAS Programmer in the world .

Code: Program

data have;
infile cards truncover expandtabs;
input Ticker Price High;
cards;
1 0.09 0.09 
1 0.08 0.09 
1 0.09 0.09
1 0.09 0.09 
1 0.07 0.09 
1 0.05 0.09 
1 0.09 0.09 
1 0.08 0.09 
1 0.07 0.09 
1 0.06 0.09 
1 0.08 0.09
1 0.09 0.09
;
run;
data temp;
 set have;
 by Ticker ;
 if first.Ticker or Price=High then n+1;
 run;
data want;
 set temp;
 by n;
 if first.n then days_since=-1;
 days_since+1;
 drop n;
run;

[merton76]

Thanks so much xia keshan. There is one more wrinkle we have to deal with. We want the clock to start ticking (i.e. "n" to change) when there is either a new high or high = price

The problem arises when we get a new high because one year has elapsed.

The problem only occurs when one year has elapsed and the price declines, as follows:

Ticker	Date	Price	High	n	days_since
33	7-Dec-00	0.09	0.12	79883	242
33	8-Dec-00	.	0.12	79883	243
33	11-Dec-00	0.09	0.12	79883	244
33	12-Dec-00	.	0.12	79883	245
33	13-Dec-00	0.09	0.12	79883	246
33	14-Dec-00	.	0.12	79883	247
33	15-Dec-00	0.09	0.12	79883	248
33	18-Dec-00	0.08	0.12	79883	249
33	19-Dec-00	.	0.12	79883	250
33	20-Dec-00	0.09	0.11	79883	251	n should change here because the high has changed. However n is only changing when High = Price
33	21-Dec-00	0.08	0.11	79883	252	Note: 251 days is the 52 week cut off (one trading year)
33	22-Dec-00	.	0.11	79883	253
33	25-Dec-00	.	0.11	79883	254
33	26-Dec-00	.	0.11	79883	255
33	27-Dec-00	0.08	0.11	79883	256
33	28-Dec-00	0.08	0.11	79883	257
33	29-Dec-00	0.09	0.11	79883	258
33	1-Jan-01	.	0.11	79883	259
33	2-Jan-01	.	0.11	79883	260
33	3-Jan-01	0.08	0.11	79883	261

I have spent have few hours trying to work this out, well at least I'm learning...

Many Thanks again!

[Ksharp]

Change it

data temp;

set have;

by Ticker ;

if first.Ticker or Price=High or High ne lag(High) then n+1;

run;

[merton76]

Thanks again xia keshan

There is still a problem. The best way to explain is in words.

Assume that we are at 1 January 2007 and the stock price is $1

The stock price goes down the next day to $0.80 and stays at that level

On 1 July 2007 the stock price rises for one day, but only to $0.90

On 1 July 2008 the stock price is $0.85. At this point, according to the code as it exists, the days since the 365 day high is 0 days. However it is, in fact, 182 days since the previous high

The challenge is creating code that can address

My thought was that we need another "n", call it "n2" that tracks the high over the previous 365 days. The solution is then the maximum of our existing n and n2.

I think this may work...

[Ksharp]

Sorry . I can't understand you .

As far as I can see.

data temp;

set have;

by Ticker ;

if first.Ticker or Price=High or ( High ne lag(High)  and price ne lag(price) ) then n+1;

run;

[merton76]

Sorry Xia, it is difficult to explain.

Essentially we need to search for the last high over the previous 365 days, then count the number of days since that high

We also need to deal with situations where the high is the same on multiple days over the last 365 days. In this case we need the most recent date

Thanks so very much for being patient with me, really appreciate it!

[Ksharp]

Sorry. You'd better start a new session and give a good example to explain it and let more people to see it .

[ndp]

looks like you need to reset day when price=high or new high or start of new year? try this:

data have;
infile cards truncover expandtabs;
input Ticker date date9. Price High;
cards;
33 07-Dec-00 0.09 0.12
33 11-Dec-00 0.09 0.12
33 13-Dec-00 0.09 0.12
33 15-Dec-00 0.09 0.12
33 18-Dec-00 0.08 0.12
33 20-Dec-00 0.09 0.11
33 21-Dec-00 0.08 0.11
33 27-Dec-00 0.08 0.11
33 28-Dec-00 0.08 0.11
33 29-Dec-00 0.09 0.11
33 03-Jan-01 0.08 0.11
run;

data have;
set have;
year=year(date);
run;

proc sort data=have;
by ticker year date ;
run;

data want;
set have;
by ticker year date;
_high=lag(high);
_date=lag(date);
retain days_since_last_high;
if first.ticker then do; _high=.; _date=.; end;
if first.ticker or first.year or price=high or high^=_high then days_since_last_high=1; *** reset to day 1;
else days_since_last_high=days_since_last_high+date-_date;
format date _date date9.;
drop _:;
run;

[andreas_lds]

So you want to set days_since to zero each time Price = High? Maybe i am unable to get the complexity of the problem, time of day and temperature may count as valid excuses, but it could be as simple as changing the if-statement to

if price = high then days_since = 0;

else days_since+1

[ChrisNZ]

data PRICES;

  do DATE='01jan2010'd to '31dec2011'd;

    PRICE=ranuni(0);

    output;

  end;

  format DATE date9.;

run;

data HIGHS_12M;

  array DATES  [730] _temporary_;

  array PRICES [730] _temporary_;

  do OBS_DAY=1 to 730;                               %* load data in arrays;

    set PRICES;

    DATES [OBS_DAY]=DATE ;

    PRICES[OBS_DAY]=PRICE;

  end;

  do OBS_DAY=1 to 730;                               %* for each observation date;

    do LOOKUP_DAY=max(OBS_DAY-365,1) to OBS_DAY;     %* look in last 365 days    ;

     MAXPRICE=max(MAXPRICE,PRICES[LOOKUP_DAY]);     %* find max price           ;

     if MAXPRICE=PRICES[LOOKUP_DAY] then

           MAXDATE=DATES[LOOKUP_DAY];                %* store matching date      ;

    end;

    DATE=DATES[OBS_DAY];                             %* save data for day        ;

    output;

    call missing(MAXPRICE,MAXDATE);                 %* reset for next obs day   ;

  end;

  format DATE MAXDATE date9.;

  keep DATE MAX:;

run;

[merton76]

Thanks Chris, do you know how we would do this by ticker?

[ChrisNZ]

Like this?

data PRICES;
  do TICKER =1 to 10;
    do DATE='01jan2010'd to '31dec2011'd;
      PRICE=ranuni(0);
      output;
    end;
  end;
  format DATE date9.;
run;

data HIGHS_12M;
  array DATES  [730] _temporary_;
  array PRICES [730] _temporary_;
  DAY=0;                                            
  do until(last.TICKER);                             %* load 1 ticker in arrays  ;
    DAY+1;                                         
    set PRICES;
    by TICKER notsorted;
    DATES [DAY]=DATE ;
    PRICES[DAY]=PRICE;
  end;
  do OBS_DAY=1 to DAY;                               %* for each observation date;
    do LOOKUP_DAY=max(OBS_DAY-365,1) to OBS_DAY;     %* scan last 365 days       ;
     MAXPRICE=max(MAXPRICE,PRICES[LOOKUP_DAY]);      %* find max price           ;
     if MAXPRICE=PRICES[LOOKUP_DAY] then
           MAXDATE=DATES[LOOKUP_DAY];                %* store matching date      ;
    end;
    DATE=DATES[OBS_DAY];                             %* save data for day        ;
    output;
    call missing(MAXPRICE,MAXDATE);                  %* reset for next obs day   ;
  end;
  format DATE MAXDATE date9.;
  keep TICKER DATE MAX:;
run;

[PJDub]

What an elegant solution to a non-trivial problem, many have tried and failed.

[ChrisNZ]

Thank you! 🙂