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

Occasional Contributor
Posts: 7

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

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

Super User
Posts: 10,761

## Re: 52 week high - problem with stocks where high does not change

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.091 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.091 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;`
Occasional Contributor
Posts: 7

## Re: 52 week high - problem with stocks where high does not change

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!

Super User
Posts: 10,761

## Re: 52 week high - problem with stocks where high does not change

Change it

data temp;

set have;

by Ticker ;

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

run;

Occasional Contributor
Posts: 7

## Re: 52 week high - problem with stocks where high does not change

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...

Super User
Posts: 10,761

## Re: 52 week high - problem with stocks where high does not change

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;

Occasional Contributor
Posts: 7

## Re: 52 week high - problem with stocks where high does not change

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!

Super User
Posts: 10,761

## Re: 52 week high - problem with stocks where high does not change

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

Contributor
Posts: 61

## Re: 52 week high - problem with stocks where high does not change

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;

Valued Guide
Posts: 557

## Re: 52 week high - problem with stocks where high does not change

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

PROC Star
Posts: 2,321

## Re: 52 week high - problem with stocks where high does not change

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;

Occasional Contributor
Posts: 7

## Re: 52 week high - problem with stocks where high does not change

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

PROC Star
Posts: 2,321

## Re: 52 week high - problem with stocks where high does not change

Like this?

Occasional Learner
Posts: 1

## Re: 52 week high - problem with stocks where high does not change

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

PROC Star
Posts: 2,321

## Re: 52 week high - problem with stocks where high does not change

[ Edited ]

Thank you!

Discussion stats
• 14 replies
• 678 views
• 5 likes
• 6 in conversation