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
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;
Please write the proper DATA step code around your example data so we know which value belongs to which variable.
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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.