How to make the firm(PERMNO) not classified again within one month

[Irenelee]

How to make the firm(PERMNO) not classified into Minhttps://drive.google.com/file/d/1grx_6p-yVMVzrzvVjdtfwyFUjRpq10W6/view?usp=sharing  again within one month? Thx!

data MK (drop=_:);
  set HL;
  by permno;

  retain _first_date_qualified . 
         _H   .                 
         _H_date .              
         _L   .                 
         _L_date .              
          PORTFOLIO .
;             
LENGTH  PORTFOLIO $11;

  if first.permno then do;
    call missing(of _:);

_first_date_qualified=intnx('year',date,1,'same')+1;

 if date < _first_date_qualified then do;
    portfolio = 'Unqualified';

	END;
 
  if _H=. then _H=P;
  if _L=. then _L=P;

  if P ^=. then do;
    if P > _H AND NOT (PORTFOLIO='MAX' AND DATE < INTNX('MONTH', _H_DATE, 1, 'SAME'))  then do;
      _H=P;
      _H_date=date;
      portfolio='Max';
    end;

    else if P < _L AND NOT (PORTFOLIO='MIN' AND DATE < INTNX('MONTH', _L_DATE, 1, 'SAME')) then do;
      _L=P;
      _L_date=date;
      portfolio='Min';
    end;

    else portfolio='Comparison';
  end;

  if date >= _first_date_qualified then do;
    H=_H;
    H_date=_H_date;
    L=_L;
    L_date=_L_date;
  end;

  format H_date L_date yymmdd10. ;

run;

10000 1987-03-02 3893 1 4.4375 0.375 0.375 Min 1986-01-27 1987-03-02
10000 1987-03-03 3893 1 4.4375 0.375 0.375 Comparison 1986-01-27 1987-03-02
10000 1987-03-04 3893 1 4.4375 0.375 0.375 Comparison 1986-01-27 1987-03-02
10000 1987-03-05 3893 1 4.4375 0.359375 0.359375 Min 1986-01-27 1987-03-05
10000 1987-03-06 3893 1 4.4375 0.359375 0.359375 Comparison 1986-01-27 1987-03-05
10000 1987-03-09 3893 1 4.4375 0.359375 0.359375 Comparison 1986-01-27 1987-03-05
10000 1987-03-10 3893 1 4.4375 0.359375 0.390625 Comparison 1986-01-27 1987-03-05
10000 1987-03-11 3893 1 4.4375 0.359375 0.390625 Comparison 1986-01-27 1987-03-05
10000 1987-03-12 3893 1 4.4375 0.359375 0.390625 Comparison 1986-01-27 1987-03-05
10000 1987-03-13 3893 1 4.4375 0.359375 0.40625 Comparison 1986-01-27 1987-03-05
10000 1987-03-16 3893 1 4.4375 0.359375 0.40625 Comparison 1986-01-27 1987-03-05
10000 1987-03-17 3893 1 4.4375 0.359375 0.390625 Comparison 1986-01-27 1987-03-05
10000 1987-03-18 3893 1 4.4375 0.359375 0.390625 Comparison 1986-01-27 1987-03-05
10000 1987-03-19 3893 1 4.4375 0.34375 0.34375 Min 1986-01-27 1987-03-19
10000 1987-03-20 3893 1 4.4375 0.34375 0.34375 Comparison 1986-01-27 1987-03-19
10000 1987-03-23 3893 1 4.4375 0.34375 0.34375 Comparison 1986-01-27 1987-03-19
10000 1987-03-24 3893 1 4.4375 0.328125 0.328125 Min 1986-01-27 1987-03-24
10000 1987-03-25 3893 1 4.4375 0.328125 0.34375 Comparison 1986-01-27 1987-03-24
10000 1987-03-26 3893 1 4.4375 0.28125 0.28125 Min 1986-01-27 1987-03-26
10000 1987-03-27 3893 1 4.4375 0.28125 0.28125 Comparison 1986-01-27 1987-03-26
10000 1987-03-30 3893 1 4.4375 0.28125 0.28125 Comparison 1986-01-27 1987-03-26
10000 1987-03-31 3893 1 4.4375 0.25 0.25 Min 1986-01-27 1987-03-31
10000 1987-04-01 3893 1 4.4375 0.234375 0.234375 Min 1986-01-27 1987-04-01
10000 1987-04-02 3893 1 4.4375 0.234375 0.234375 Comparison 1986-01-27 1987-04-01

[Kurt_Bremser]

Please write the proper DATA step code around your example data so we know which value belongs to which variable.

[Irenelee · Accepted solution]

Got it now! thx!

DM'LOG; CLEAR; OUT; CLEAR; ODSRESULTS; CLEAR;';
%LET FOLDER=%STR(C:\USERS\ALAIN\ONEDRIVE\桌面\HIGH);
LIBNAME HIGH "&FOLDER";

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

DATA HH;
	SET	HH;
	IF _N_ <=1000;
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 0; 
    P=ABS(DIVIDE (PRC, CFACPR));
    IF FIRST.PERMNO THEN H = P; 
    IF FIRST.PERMNO THEN L = P;
    H = MAX(H, P); 
    L = MIN(L, P);
 DROP  PRC CFACPR;
   RUN; /* PERMNO DATE  SHROUT CFACSHR H L P */

data MK (drop=_:);
  set HL;
  BY PERMNO date; 

  retain _first_date_qualified . 
         _H   .                 
         _H_date .              
         _L   .                 
         _L_date .              
          PORTFOLIO 
;             
LENGTH  PORTFOLIO $11;

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

  if _H=. then _H=P;
  if _L=. then _L=P;

  if P ^=. then do;
    if P > _H and date >= INTNX('MONTH', _H_DATE, 1, 'SAME')) then do;
      _H=P;
      _H_date=date;
      portfolio='Max';
    end;

    else if P < _L and date >= INTNX('MONTH', _L_DATE, 1, 'SAME')) then do;
      _L=P;
      _L_date=date;
      portfolio='Min';
    end;

    else portfolio='Comparison';
  end;

  if date >= _first_date_qualified then do;
      H = _H;
      H_date = _H_date;
      L = _L;
      L_date = _L_date;
  end;

  if date < _first_date_qualified then do;
    portfolio = 'Unqualified';
  end;

  format H_date L_date yymmdd10. ;

run;

DATA MK;
  SET MK(OBS=500);
  PUT (_ALL_) (+0);
RUN;