create a column of historical high price

[Irenelee]

hi hi, Good Samaritan,

How may I create a column of the historical high price (HH) since 01Jan1963?

I plan to get the historical high price of each of the firm BETWEEN '01Jan1926'd and '31Dec1962'd (OHH) on first column for 01Jan1963 , then if everyday every stock's price is higher than OHH, then HH is updated with latest price.  

DATA CRSP1;
	SET  CRSP;
	PRC=ABS(PRC);
	IF SHRCD^=10 AND SHRCD^=11 THEN DELETE;
	IF EXCHCD^=1 AND EXCHCD^=2 AND EXCHCD^=3 AND EXCHCD^=31 AND EXCHCD^=32 AND EXCHCD^=33 THEN DELETE;
	
KEEP PERMNO DATE PRC CFACPR SHROUT CFACSHR;
RUN;

PROC SORT DATA= CRSP1;
	BY PERMNO DATE;
RUN;

DATA FILTER;
    SET CRSP1;
    WHERE DATE BETWEEN '01Jan1926'd and '31Dec1962'd;
RUN;

PROC SUMMARY DATA=FILTER NOPRINT;
    BY PERMNO;
    OUTPUT OUT=OLD(DROP=_:) 
           MAX(PRC) = OHH
           MIN(PRC) = OLL;
RUN;

DATA FINAL_DATA;
    MERGE FILTER OLD (KEEP=PERMNO DATE OHH OLL);
    BY PERMNO;
    WHERE DATE =  '31Dec1962'd;
RUN;

[Irenelee]

I want to make out the following things, but crash at the beginning. 

sample data by this two years

https://drive.google.com/file/d/1DAPXaUmE5xKvJmnfV7vz-MVF9MGVfSDk/view 

[Patrick]

If I understood you right then below code returning the rolling max per permno should be what you're after.

libname src 'c:\temp';

proc sort 
  data=src.sampledata(keep  =shrcd exchcd permno date prc cfacpr shrout cfacshr
                      where =( shrcd in (10,11) and exchcd in (1,2,3,31,32,33) )
                      ) 
  out=crsp1(drop=shrcd exchcd );
  by permno date;
run;

data want;
  set crsp1;
  by permno date;
  retain hh;
  if first.permno then call missing(hh);
  hh=max(abs(prc),hh);
  if date>='01jan1963'd then 
    do;
      /* here potentially more logic TBD */
      output;
    end;
run;

Should you also struggle to get the rest of the logic going then please provide some explanation additionally to your screenshot.

[Irenelee]

provide some explanation additionally to my screenshot

1. DEFINE A FIRM’S HISTORICAL HIGH PRICE OF DATE OF 1962-12-31(1962HH) AS HIGHEST PRICE FROM 1926-01-01 TO 1962-12-31.
2. DEFINE HISTORICAL HIGH PRICE SINCE 1963-01-01 TO 2023-12-31 IF PRICE OF 1963-01-01 BREAK THROUGH (1962HH), THEN HISTORICAL HIGH PRICE =THAT FIRM-DATE’S PRICE, ELSE 1962HH.
3. FLAG BREAK UP THROUGH HISTORICAL HIGH (THE FIRST PORTFOLIO MAX )  : IF THAT FIRM-DATE’S PRICE> HISTORICAL HIGH PRICE AND THE NEW FLAG HAVE TO PASS THROUGH ONE MONTH.
4. DEFINE EACH FIRM’S LAST MONTH’S MARKET VALUE= END OF LAST MONTH’S PRICE * END OF LAST MONTH’S SHARES OUTSTANDING
5. PROC SORT DATA     BY DATE PERMNO MARKET VALUE(SMALL TO BIG), AND GIVE EACH DATE PERMNO MARKET VALUE A RANK(1 T0 X), AND GIVE THE PERCENTILE BY RANK/TOTAL NUMBERS X. THE 0~<25% ARE GROUP BY SMALL SIZE, THE 25%~<50% ARE GROUP BY MEDIUM SIZE, THE 50%~100% ARE GROUP BY BIG SIZE.
6. CALCULATE THE EQUAL-WEIGHTED (EW) AND VALUE-WEIGHTED (VW) COMPOUND DAILY RETURN FOR THE SUBSEQUENT WEEK AND MONTH BEGINNING THE DAY AFTER A FIRM IS ASSIGNED TO THE MAX, MIN, OR COMPARISON PORTFOLIO BY SMALL, MEDIUM, BIG SIZE.
7. CLACULATE THE INTERCEPT FROM A REGRESSION OF THE DAILY PORTFOLIO RETURN ON FAMA AND FRENCH (1993) FACTORS AND THE CARHART (1997) MOMENTUM FACTOR.

[Patrick · Accepted solution]

@Irenelee 

I believe below code already populates variables for your point 1 & 2.

I also added some code to demonstrate how you can populate a variable with max vals of a previous month which I believe is what you need to implement some of your other requirements. 

I'm not going to try and provide ready made code for all your requirements. I suggest you give it a go and then eventually ask new and targeted questions for the bits you can't solve (also providing some of your code even if not yet working). 

libname src 'c:\temp';

proc sort 
  data=src.sampledata(keep  =shrcd exchcd permno date prc cfacpr shrout cfacshr
                      where =( shrcd in (10,11) and exchcd in (1,2,3,31,32,33) )
                      ) 
  out=crsp1(drop=shrcd exchcd );
  by permno date;
run;

data want(drop=_:);
  set crsp1;
  by permno date;
  if first.permno then call missing(hh);

  /* max val up to date in current row */
  retain hh;
  hh=max(abs(prc),hh);

  /* max val by eoy 1962 */
  retain hh_eoy_1962;
  if date='31Dec1962'd or first.permno and date>'31Dec1962'd then hh_eoy_1962=hh;
  
  if date>='01jan1963'd then 
    do;
      /* here potentially more logic TBD */
      output;
    end;

  /* max vals end of month for use in iteration of data step with dates for next month */
  retain _max_eom;
  _max_eom=max(_max_eom,abs(prc));
  if date=intnx('month',date,0,'e') then
    do;
      retain max_eom;
      max_eom=_max_eom;
      call missing(_max_eom);
    end;
run;

[Irenelee]

Thank you Patrick,

Although my study begins in 1963, I use the CRSP sample start date in 1926 to determine the highest historical price attained by a stock.


This study identifies the date on which the security breaks out of the prior trading range and track the security for the ensuing month to assess whether breaking out of the trading range appears to predict future returns. To compute returns, this study follows the calendar-time portfolio approach, placing a firm-day into the MAX (MIN) portfolio if the stock is trading at the historical high (low) and has not been at the high or low in the last month. Average daily portfolio buy-and-hold returns (in %) are measured for the subsequent week and month beginning the day after a firm is assigned to the MAX, MIN, or comparison portfolio. Firms are assigned to a size quartile group based on their previous month’s market value of equity relative to the distribution of market value of equity across all three exchanges. Net returns are adjusted for the comparison portfolio returns. Equal-weighted (EW) and value-weighted (VW) portfolio returns are calculated daily, and the portfolio is rebalanced at the end each day. EW returns are compounded to reduce bid ask bias. The four-factor adjusted return is the intercept from a regression of the daily portfolio return on Fama and French (1993) factors and the Carhart (1997) momentum factor.

[mkeintz]

It looks to me like you want to create pre-1963 historic adjusted highs and low, and then compare each post-1962 observation to those historic extremes.  You can do that in one pass of the crsp data, which is typically already sorted by PERMNO/DATE.

data want ;
  set crsp (where=(shrcd IN (10,11) and exchcd in (1,2,3,31,32,33));
  by permno date;

  prc_split_adjusted =abs(prc)/cfacpr;

  retain ohh   ohdate   /*Pre-1963 historical adjusted high and date of its latest occurence*/
         ohprc ohcfacpr /*Nominal PRC and CFACPR on OHDATE */
         oll   oldate   /*Pre-1963 historical adjusted low and date of its latest occurence*/
         olprc ohlfacpr /*Nominal PRC and CFACPR on OLDATE */
         ;
  format ohdate oldate date9.;

  if first.permno then call missing(of oh:, of ol:);

  if date<'01jan1963'd then do;
    if prc_split_adjusted=max(prc_split_adjusted,ohh) then do;
      ohh=prc_split_adjusted;
      ohdate=date;
    end;
    if prc_split_adjusted=min(prc_split_adjusted,oll) then do;
      oll=prc_split_adjusted;
      oldate=date;
    end;
  end;

  if date>='01jan1963'd;  /* This is a "subsetting IF" */
  ** Do your code for comparing post-1962 to OHH and OLL here **;
run;

[Irenelee]

Thank you mkeintz,

Yes, I actually want to compare with historical high since 1963.  (But the historical high price can be got from 1926~), and the historical high could be higher day by day after 1963.

IF HISTORICAL HIGH PRICE SINCE 1963-01-01 TO 2023-12-31 IF PRICE OF 1963-01-01 BREAK THROUGH (1962HH), THEN HISTORICAL HIGH PRICE IS UPDATED *(MAYBE EVERYDAY)=THAT FIRM-DATE’S PRICE, ELSE 1962HH.

FLAG BREAK UP THROUGH HISTORICAL HIGH  : IF THAT FIRM-DATE’S PRICE> HISTORICAL HIGH PRICE AND THE NEW FLAG HAVE TO PASS THROUGH ONE MONTH