Thanks!
All Permno should be categorized by market value EVERYDAY by end of last month (previous_MV) into three groups labeled as small, mid, and big.
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 MV;
SET MK;
BY PERMNO DATE;
RETAIN
MV
LAST_MONTH_END;
LAST_MONTH_END = INTNX('MONTH', DATE, -1, 'END');
FORMAT LAST_MONTH_END YYMMDD10. ;
MV = P * (SHROUT*CFACSHR) ;
RUN;
data LMV;
set MV ;
by permno date ;
lag_MV = lag(MV);
if first.permno then call missing(lag_MV,previous_MV);
else if month(date) ne month(lag(date)) then previous_MV=lag_MV;
retain previous_MV;
drop lag_MV;
run;
PROC SORT DATA=LMV;
BY DATE PERMNO DESCENDING previous_MV;
RUN;
PROC RANK DATA=LMV OUT=RANKED_DATA GROUPS=4TIES=LOW;
BY DATE;
VAR previous_MV;
RANKS QUARTILE;
RUN;
DATA SMB;
SET RANKED_DATA;
RETAIN
SIZE
;
LENGTH SIZE $5;
IF QUARTILE=1THEN SIZE= 'SMALL';
ELSE IF QUARTILE=2 THEN SIZE= 'MID';
ELSE SIZE = 'BIG';
RUN;
https://drive.google.com/file/d/1grx_6p-yVMVzrzvVjdtfwyFUjRpq10W6/view?usp=drive_link
How to Concatenate Values
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.
-
1035 views
-
0 likes
-
-
