☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Quartz | Level 8

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Quartz | Level 8

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

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;

``````
2 REPLIES 2
Super User

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

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

Quartz | Level 8

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

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;

``````
Discussion stats
• 2 replies
• 291 views
• 1 like
• 2 in conversation