After breaking through the historical high, calculate the six-month return of that stock

[Irenelee]

1.May I know why Variable 'last.breakhigh'n is uninitialized?

2.How may I solve MERGE statement has more than one data set with repeats of BY values?

Thx!!

DM'LOG; CLEAR; OUT; CLEAR; ODSRESULTS; CLEAR;';

%let folder=%str(C:\Users\SC\Desktop\HIGH);

LIBNAME HIGH "&folder";

DATA CRSP;

      SET   HIGH.DAILY;

RUN;

DATA CRSP;

      SET  CRSP;

      PRC=ABS(PRC);

      IF SHRCD^=10 AND SHRCD^=11 THEN DELETE;

      IF RET=-66 THEN RET=.;

      IF RET=-77 THEN RET=.;

      IF RET=-88 THEN RET=.;

      IF RET=-99 THEN RET=.;

KEEP PERMNO DATE PRC RET;

RUN;

PROC SORT DATA= CRSP;

      BY PERMNO DATE;

RUN;

data highs;

    set CRSP;

    by PERMNO date;

    retain highest 0;

    if first.PERMNO then highest = PRC;

    else highest = max(highest, PRC);

    if PRC > highest then do;

        BREAKHIGH = 1;

        six_months_later = intnx('month', date, 6, 'same');

    end;

    else BREAKHIGH = 0;

       if BREAKHIGH = 1 then last_break_date = date;

    else if last.breakhigh then last_break_date = .;

run;

data Returns;

    merge highs(in=a) CRSP(in=b rename=(date=date_future PRC=PRC_future));

    by PERMNO;

    if a and b and intck('month', last_break_date, date_future) = 6 then do;

        six_month_return = (PRC_future - PRC) / PRC;

    end;

run;

[maguiremq · Accepted solution]

You can't use LAST.Breakhigh because it isn't established yet in the code. In other words, it does not exist in your CSRP data set. The variable exists in HIGHS, so you would need to use FIRST/LAST on the next data set you create. This comes down to how the PDV works. 

FIRST/LAST processing can occur when the variable exists on the input data set, you have sorted it by the desired variables, and you include it on the BY statement after the SET statement.

Also, please provide usable data for us. You can use the data to data step macro.

[Irenelee]

Thank you for your reply, data to data step macro seem hard to me to understand how to create, sry.

May I further know how to define last.breakhigh would be ok?

[Irenelee]

I put short version data via google, is it easier for help? Thx a lot!

https://drive.google.com/file/d/1ayhUMgH0j2IuIykNpgUt6WbPg-7Gx-JA/view

[mkeintz]

There are a number of issues with your management of the CRSP data, some minor, some major.  Here's a couple:

1.   (Minor):  Instead of copying dataset high.daily to dataset work.crsp, and then modifying work.crsp with a second data step, do the second data step with a SET HIGH.DAILY instead of SET CRSP statement.   Save resources.
   
   
   
2. (Major):  Your code below is meant to make a dummy variable BREAKHIGH, but it will never get a value of 1.  The code you present is:

data highs;
    set CRSP;
    by PERMNO date;

    retain highest 0;
    if first.PERMNO then highest = PRC;

    else highest = max(highest, PRC);
    if PRC > highest then do;
        BREAKHIGH = 1;
        six_months_later = intnx('month', date, 6, 'same');
    end;
    else BREAKHIGH = 0;

    if BREAKHIGH = 1 then last_break_date = date;
    else if last.breakhigh then last_break_date = .;
run;

Since you set PRC to max(highest,PRC) prior to your "if PRC > highest then do;" statement, the if test is never true.  And breakthrough will never take the value of 1, nor will you ever assign a value to six_months_later.

So here are some questions:

• I assume that breakthrough high means "highest PRC so far", yes?  In other words, you want to compare PRC to the highest PRECEDING PRC, correct?
• Can the first obs be a breakthrough high (since there is no preceding history)?
• What is the purpose of the code below?

if BREAKHIGH = 1 then last_break_date = date;
    else if last.breakhigh then last_break_date = .;

Finaly please provide an example of what you want the OUTPUT to look like.  I can't tell whether you need dataset HIGHs to only have breakthrough=1 obs, or needs to have all obs.

[Irenelee]

Thank you for your reply!

yes, breakthrough high means "highest PRC so far",

I want to compare PRC to the highest PRECEDING PRC,and divide into 2 group.

and run the excess return by 3 factor model by this two group.

the first obs be a breakthrough high should be after one year.

What the output I want to produce is as below, instead of by breakthough 52-weeks high, but by historical high. Thx!!

[Irenelee]

Thank you!

I want SAS program that define the highest historical prices of each stock from 1963 to 2023, and create a new variable BREAKHIGH to represent when the closing price breaks through the historical high. After breaking through the historical high, calculate the six-month return of that stock. I want to divide into 2 group by breakthrough historical high and the rest,and run the excess return by 3/4/5 factor model by this two group.
the first obs be a breakthrough high should be after one year historical data.

[mkeintz]

More comments:

1. Stocks can split, or similar events can take place over the stock price history.  CRSP has a variable CFACPR (cumulative adjustment for prices) which would allow you to adjust prices so that you don't get a misleading return when such an event happens.  Why are you not adjusting prices?  If there is a stock split (say 2 for 1), the new stock would have to exceed TWICE the previous historic high to qualify as a breakthrough in the absence of such a n adjustment.
   
   
2. Presumably a stock can have multiple breakthrough high's over its history.  Do you want 6-month returns for each such event?    What if there is a 5-day sequence of consecutive break throughs?  Do you want a six-month return for each (i.e. 5 overlapping 6 month returns)?  Or for only the last one?
   
   
3. You apparently want to compare six-month returns for historical highs vs "the rest".  How will you select the rest?  Do you plan on generating tens of thousands of (overlapping) six-month returns for all the other dates?  Or will you be more selective?  Will "the rest" be prohibited from overlapping any six-month period after a historic high?
   
   
4. What if a historical high occurs less than 6 months prior to your last date?  Will such dates be ignored?