make sure at least 1 year of available daily price data exist

[Irenelee]

it seems date already printed easily to be understood for the moment, right? So I may omit the process of the date format?

691 data HL;
692 set HL(obs=10);
693 put (_all_) (+0);
694 run;

10000 1986-01-07 -2.56250 3680 1 1 2.5625 2.5625
10000 1986-01-08 -2.50000 3680 1 1 2.5 2.5
10000 1986-01-09 -2.50000 3680 1 1 2.5 2.5
10000 1986-01-10 -2.50000 3680 1 1 2.5 2.5
10000 1986-01-13 -2.62500 3680 1 1 2.625 2.625
10000 1986-01-14 -2.75000 3680 1 1 2.75 2.75
10000 1986-01-15 -2.87500 3680 1 1 2.875 2.875
10000 1986-01-16 -3.00000 3680 1 1 3 3
10000 1986-01-17 -3.00000 3680 1 1 3 3
10000 1986-01-20 -3.00000 3680 1 1 3 3

[Tom · Accepted solution]

Great. So the next step is to take that text and convert it into a SAS program that you can share so anyone that wants to help you can have your data.

Not sure what your variable names are but perhaps something like this:

data have;
  input firm $ date :yymmdd. price volume a b c d ;
  format date yymmdd10.;
cards;
10000 1986-01-07 -2.56250 3680 1 1 2.5625 2.5625
10000 1986-01-08 -2.50000 3680 1 1 2.5 2.5
10000 1986-01-09 -2.50000 3680 1 1 2.5 2.5
10000 1986-01-10 -2.50000 3680 1 1 2.5 2.5
10000 1986-01-13 -2.62500 3680 1 1 2.625 2.625
10000 1986-01-14 -2.75000 3680 1 1 2.75 2.75
10000 1986-01-15 -2.87500 3680 1 1 2.875 2.875
10000 1986-01-16 -3.00000 3680 1 1 3 3
10000 1986-01-17 -3.00000 3680 1 1 3 3
10000 1986-01-20 -3.00000 3680 1 1 3 3
;

Make sure to use the menu items that give up pop-up windows to enter/edit the text for your program (Insert SAS Program) or other text (Insert Code).  Otherwise the forum will think you meant that as paragraphs of text to be reflowed to fit the viewers browser window size.

[Irenelee]

Thx!
Do you know why No matching DO/SELECT statement
ERROR 161-185: No matching DO/SELECT statement.

DATA FLAG;
 SET HL;
 BY PERMNO DATE;
 retain FLAG;
  if first.permno then do;
	counter = 0;
	first_date = date; 
  end;
	day_of_year = intnx('day', date, first_date);
  if day_of_year <= 365 and not missing(day_of_year) then FLAG = 'UNQUALIFIED';

 if PRC > LAG(H) AND date >= '01JAN1963'd then FLAG = MAX; 
 if PRC < LAG(L) AND date >= '01JAN1963'd then FLAG = MIN; 
 else FLAG = COMPARISON;
 end;
run;

[Irenelee]

DATA HH;
	SET	HIGH.HH;
	IF CFACPR=0 THEN DELETE;
RUN;

PROC SORT 
  DATA=HH (KEEP  =SHRCD EXCHCD PERMNO DATE PRC CFACPR SHROUT CFACSHR
                      WHERE =( SHRCD IN (10,11) AND EXCHCD IN (1,2,3,31,32,33) )
                      ) 

OUT=HL(DROP=SHRCD EXCHCD );
  BY PERMNO DATE;
RUN;

DATA HL;
 SET HL;
 BY PERMNO DATE;
 retain H L;
 H = max(ABS( divide (PRC, CFACPR)));
 L = min(ABS( divide (PRC, CFACPR)));
 RUN;

[Irenelee]

variable names are
PERMNO DATE PRC CFACPR SHROUT CFACSHR H L

[Tom]

---

@Irenelee wrote:
variable names are
PERMNO DATE PRC CFACPR SHROUT CFACSHR H L

---

Ok.   Is this supposed to be stock market data? So what do those names mean?  

Clearly the H and L are NOT the same as the H that you talked about before.  Perhaps they are the high and low price seen during that trading day?  As opposed to the highest price ever seen.  Or the highest closing price ever seen?  Or the highest price in the last year?  Or last 5 years? Or ....

Once you have clearly described your inputs then you need to do the same for the output you want to create from that input.  So provide the result you want to get for that input.  Here you might need to begin hand crafting better example input data to clearly demonstrate some of the boundary and/or unusual situations that you need help with writing code to handle.

[mkeintz]

---

@Tom wrote:

---

@Irenelee wrote:
variable names are
PERMNO DATE PRC CFACPR SHROUT CFACSHR H L

---

Ok.   Is this supposed to be stock market data? So what do those names mean?  

Clearly the H and L are NOT the same as the H that you talked about before.

@Tom   I can answer the easy part.   Yes, it is stock market data distributed by CRSP (Center for Research in Stock Prices).

• PERMNO                       Unique ID number assigned by CRSP to each stock.
• DATE                              Trading Date
• PRC                               The stock closing price on DATE.   If PRC is negative, there was no trading on that date, so PRC is the "negative" of the closing BID/ASK midpoint on that DATE
• CFACPR                       An adjustment factor, that when applied to PRC allows consistent comparison of prices over the entire stock history, even when that stock has multiple events such as stock splits.
• SHROUT                       Shares outstanding.    In general, market value= SHROUT*PRC
• CFACSHR                     An ajustment factor for SHROUT, similar to the function of CFACPR when dealing with PRC
• H and L                         are not standard CRSP variables - as you surmise probably a user-generated value (maybe a high and low over some time period.

Data from CRSP are always provided to users sorted by PERMNO/DATE, and no DATE has more than one observation (edited addition: for a given PERMNO).

The hard part:   @Irenelee -help us help you.  Please show a sample of exactly what the resulting data set should look like.  I too, do not understand how you want highs recorded.  (Once per PERMNO? once for every new historic high?  Do you want one obs per day in your output dataset?    And then you have mentioned 52-week ranges.  Do you want, for every observation, to also have a rolling 52-week range?)

[Irenelee]

Thank you!

I create a new Column named “H” which is a firm’s daily rolling historical high price.

the resulting data set should look like the following

[Irenelee]

“H” which is a firm’s daily rolling historical high price, then I may create a new Column named FLAG.

• Tag the daily price data of its first day to 365th day to the flag of UNQUALIFIED ( by PERMNO DATE)  for example,  if a firm is IPOed on 1962/10/1, this firm can only be classified “unqualified” for the 1962/10/1 to 1963/10/1.
• The price of a firm by that date > LAG(H), then classified into “Max” portfolio after 1963/1/1, vice versa “Min”, The remaining classified into third portfolio flagged “COMPARISON”

[Irenelee]

Then I may watch the monthly return after the stock is classified into Max or Min portfolio, so the same stock can not be reclassified into Max or Min within one month after it was classified into Max or Min. for example, if a firm is classified into “Max” on 1963/1/1, then this firm can only classified into “Max” again after 1963/2/1.

[Irenelee]

I want to show the following table at the end, but with historical high, instead of 52 wks high. 

[mkeintz]

Editted note:  The most important statement in my original email below is "This program is untested".   The two statements following the "IF FIRST.PERMNO" test should have been reversed, and I have done so now.  The "call missing" should precede assignment of the value for _first_date_qualified.

If a permno has a highest (adjusted) PRC of, say, 100 over the first year (the "unqualified" period), but afterwards never exceeds 80, will H be 100? or 80? 

In other words, is the unqualified period price data used to track the value of H  (and L)?

This matters, because if it is NOT used (i.e. H=80), then each and every PERMNO will have an H event on the first date after the unqualified period.  The program below assumes the unqualified period price values are included in determining a new max or min during the qualified period:

data want (drop=_:);
  set have;
  by permno;

  retain _first_date_qualified . /* One year after first date             */
         _H   .                  /* Highest Price since day 1             */
         _H_date .               /* First date with current value of H    */
         _L   .                  /* Lowest Price since day 1              */
         _L_date .               /* First date with current value of L    */
         ;

  length portfolio $11 ;         /*Each obs will be "Unqualified","Max","Min", or "Comparison"*/

  if prc^=. then prc_adjusted =  abs(prc/cfacpr) ;

  if first.permno then do;
    call missing(of _:);
    _first_date_qualified=intnx('year',date,1,'same')+1;
  end;

  if _H=. then _H=prc_adjusted;
  if _L=. then _L=prc_adjusted;

  if prc_adjusted ^=. then do;
    if prc_adjusted > _H then do;   /*If New High ...*/
      _H=prc_adjusted;
      _H_date=date;
      portfolio='Max';
    end;
    else if prc_adjusted < _L then do;  /*If New Low ...*/
      _L=prc_adjusted;
      _L_date=date;
      portfolio='Min';
    end;
  end;

  if date >= _first_date_qualified then do;
    H=_H;
    H_date=_H_date;
    L=_L;
    L_date=_L_date;
    if portfolio=' ' then portfolio='Comparison';
  end;
  else portfolio='Unqualified';
  format H_date L_date yymmdd10. ;
run;

This program is untested.  It produces, for each observation, six new variables:  PRC_ADJUSTED (adjusted by cfacpr), PORTFOLIO ("Max", "Min", "Comparison", "Unqualified"),  H, H_Date, L, and L_date.  H_date and L_date are the earliest dates in which a new high or low is encountered.

If you have a new high on a given date, and a set of later dates with the same PRC, only the first instance has portfolio="Max".    So you can have a set of dates with constant PRC_ADJUSTED=H, but only the first one has H_DATE=DATE.  

[Irenelee]

Thank you so much!!

May I know why no one is flagged as 'Max' or 'Min'? 
why first_date_qualified keep changing, not fixed by the first PERMNO?

10000 1986-01-07 3680 1 2.5625 2.5625 2.5625 UNQUALIFIED 9503
10000 1986-01-08 3680 1 2.5625 2.5 2.5 UNQUALIFIED 9504


10000 1987-01-06 3843 1 4.4375 0.46875 0.46875 UNQUALIFIED 9867
10000 1987-01-07 3843 1 4.4375 0.46875 0.46875 UNQUALIFIED 9868
10000 1987-01-08 3843 1 4.4375 0.46875 0.46875 UNQUALIFIED 9869

10000 1987-06-12 . . 4.4375 0.203125 . UNQUALIFIED 10024
10001 1986-01-09 985 3 1.9166666667 1.9166666667 1.9166666667 UNQUALIFIED 9505

[Irenelee]

Thank you mkeintz!
If a permno has a highest (adjusted) PRC of, say, 100 over the first year (the "unqualified" period), but afterwards never exceeds 80,  H will be 100.

the unqualified period price data also used to track the value of H  (and L).

Yes, the unqualified period price values are included in determining a new max or min during the qualified period.

[Irenelee]

Thank you mkeintz!

Do you know how to correct the following code?
IF LAG(FLAG) IN ('Max', 'Min') AND DATE < INTNX('MONTH', LAG(DATE), 1, 'SAME') THEN FLAG = LAG(FLAG);

Because we will watch the monthly return after the stock is classified into Max or Min portfolio, so the same stock can not be reclassified into Max or Min within one month after it was classified into Max or Min.  for example, if a firm is classified into “Max” on 1963/1/1, then this firm can only classified into “Max” again after 1963/2/1.
(i.e. since we need to calculate the one-month return after breaking through the historical high/low, the group that is classified as breaking through the historical high/low must wait another month before it can be classified as the breaking through the historical high group again, because it has already been included in the breaking through the historical high/low group, it will not be classified again.)