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