BookmarkSubscribeRSS Feed
merton76
Fluorite | Level 6

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:

TickerPriceHighdays_since
10.090.090
10.080.091
10.090.092
10.090.093
10.070.094
10.050.095
10.090.096
10.080.097
10.070.098
10.060.099
10.080.0910
10.090.0911

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:

TickerPriceHighdays_since
10.090.090
10.080.091
10.090.090
10.090.090
10.070.091
10.050.092
10.090.090
10.080.091
10.070.092
10.060.093
10.080.094
10.090.090

Any direction would be greatly assisted!

Many thanks

14 REPLIES 14
Ksharp
Super User

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
Fluorite | Level 6

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:

TickerDatePriceHighndays_since
337-Dec-000.090.1279883242
338-Dec-00.0.1279883243
3311-Dec-000.090.1279883244
3312-Dec-00.0.1279883245
3313-Dec-000.090.1279883246
3314-Dec-00.0.1279883247
3315-Dec-000.090.1279883248
3318-Dec-000.080.1279883249
3319-Dec-00.0.1279883250
3320-Dec-000.090.1179883251n should change here because the high has changed. However n is only changing when High = Price
3321-Dec-000.080.1179883252Note: 251 days is the 52 week cut off (one trading year)
3322-Dec-00.0.1179883253
3325-Dec-00.0.1179883254
3326-Dec-00.0.1179883255
3327-Dec-000.080.1179883256
3328-Dec-000.080.1179883257
3329-Dec-000.090.1179883258
331-Jan-01.0.1179883259
332-Jan-01.0.1179883260
333-Jan-010.080.1179883261

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

Many Thanks again!

Ksharp
Super User

Change it

data temp;

set have;

by Ticker ;

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

run;

merton76
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

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
Super User

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

ndp
Quartz | Level 8 ndp
Quartz | Level 8

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
Jade | Level 19

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
Tourmaline | Level 20

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
Fluorite | Level 6

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

ChrisNZ
Tourmaline | Level 20
Like this?
 

PJDub
Calcite | Level 5

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

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
  • 14 replies
  • 1902 views
  • 5 likes
  • 6 in conversation