topic categorize market values into three groups labeled as small, mid, and big in SAS Programming

categorize market values into three groups labeled as small, mid, and big

Irenelee — Wed, 01 May 2024 16:52:33 GMT

Would u like to help me to categorize market values into three groups labeled as small, mid, and big?

FIRMS ARE ASSIGNED TO A SIZE QUARTILE GROUP BASED ON THEIR PREVIOUS MONTH’S MARKET VALUE OF EQUITY

THE 0~<25% ARE GROUP BY SMALL SIZE, THE 25%~<50% ARE GROUP BY MEDIUM SIZE, THE 50%~100% ARE GROUP BY BIG SIZE.

PROC SORT DATA BY DATE PERMNO MARKET VALUE(SMALL TO BIG), AND GIVE EACH DATE PERMNO MARKET VALUE.

DATA SORTED;
  SET SORTED;
  BY DATE PERMNO;
  
  IF LAST.PERMNO THEN DO;
    CALL STREAMINIT(123); 
    RANK = RANKTIE(LAST_MONTH_MV, 'DESCENDING'); 
    PCTILE = RANK / (LAST.PERMNO + 1); 
    
    IF PCTILE < 0.25 THEN SIZE_GROUP = 'SMALL';
    ELSE IF PCTILE < 0.50 THEN SIZE_GROUP = 'MEDIUM';
    ELSE SIZE_GROUP = 'BIG';
  END;
RUN;

ERROR 68-185: The function RANKTIE is unknown, or cannot be accessed.

Re: categorize market values into three groups labeled as small, mid, and big

Irenelee — Wed, 01 May 2024 16:54:25 GMT

8g DATA

8g data

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;

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;

  RETAIN _FIRST_DATE_QUALIFIED . 
         _H .                  
         _H_DATE .           
         _L .                 
         _L_DATE .             
          PORTFOLIO $11. ;             

  IF FIRST.PERMNO THEN DO;
    CALL MISSING(OF _:);
    _FIRST_DATE_QUALIFIED=INTNX('YEAR', DATE, 1, 'SAME')+1;
    PORTFOLIO = ' ';
  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;
  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;

DATA MV;
  SET MK;
  FORMAT LAST_MONTH DATE9.;
  LAST_MONTH = INTNX('MONTH', DATE, -1, 'end');
  LAST_MONTH_MV = P * (SHROUT*CFACSHR);
RUN;

PROC SORT DATA = MV OUT = SORTED;
  BY DATE PERMNO LAST_MONTH_MV;
RUN;

DATA SORTED;
  SET SORTED(OBS=50);
  PUT (_ALL_) (+0);
RUN;

DATA SORTED;
  SET SORTED;
  BY DATE PERMNO;
  
  IF LAST.PERMNO THEN DO;
    CALL STREAMINIT(123); 
    RANK = RANKTIE(LAST_MONTH_MV, 'DESCENDING'); 
    PCTILE = RANK / (LAST.PERMNO + 1); 
    
    IF PCTILE < 0.25 THEN SIZE_GROUP = 'SMALL';
    ELSE IF PCTILE < 0.50 THEN SIZE_GROUP = 'MEDIUM';
    ELSE SIZE_GROUP = 'BIG';
  END;
RUN;


PROC SORT DATA = SORTED;
  BY DATE SIZE_GROUP FLAG;
RUN;

DATA EW_RETURNS VW_RETURNS;
  SET SORTED;
  BY DATE SIZE_GROUP FLAG;
  
  RETAIN EW_RET VW_RET 0;
  
  IF FIRST.FLAG THEN DO;
    EW_RET = 1;
    VW_RET = 1;
    SUM_MV = 0;
  END;
  
  EW_RET = EW_RET * (1 + RET);
  SUM_MV = SUM_MV + LAST_MONTH_MV;
  VW_RET = VW_RET * (1 + RET * LAST_MONTH_MV / SUM_MV);
  
  IF LAST.FLAG THEN DO;
    EW_RETURN = EW_RET - 1;
    VW_RETURN = VW_RET - 1;
    OUTPUT EW_RETURNS;
    OUTPUT VW_RETURNS;
  END;
RUN;

PROC REGRESS DATA = EW_RETURNS;
  MODEL EW_RETURN = MKT_RF SMB HML UMD / NOINT;
  OUTPUT OUT = EW_ALPHA 
         P = EW_ALPHA;
RUN;

PROC REGRESS DATA = VW_RETURNS;
  MODEL VW_RETURN = MKT_RF SMB HML UMD / NOINT;
  OUTPUT OUT = VW_ALPHA
         P = VW_ALPHA;
RUN;

Re: categorize market values into three groups labeled as small, mid, and big

Irenelee — Wed, 01 May 2024 16:55:52 GMT

10000 1986-01-07 3680 1 2.5625 2.5625 2.5625 . Unqualified . . 31DEC1985 9430
10000 1986-01-08 3680 1 2.5625 2.5 2.5 . Unqualified . . 31DEC1985 9200
10000 1986-01-09 3680 1 2.5625 2.5 2.5 . Unqualified . . 31DEC1985 9200
10001 1986-01-09 985 3 1.9166666667 1.9166666667 1.9166666667 . Unqualified . . 31DEC1985 5663.75
10000 1986-01-10 3680 1 2.5625 2.5 2.5 . Unqualified . . 31DEC1985 9200
10001 1986-01-10 985 3 1.9583333333 1.9166666667 1.9583333333 . Unqualified . . 31DEC1985 5786.875
10000 1986-01-13 3680 1 2.625 2.5 2.625 . Unqualified . . 31DEC1985 9660
10001 1986-01-13 985 3 1.9583333333 1.9166666667 1.9583333333 . Unqualified . . 31DEC1985 5786.875
10000 1986-01-14 3680 1 2.75 2.5 2.75 . Unqualified . . 31DEC1985 10120
10001 1986-01-14 985 3 1.9583333333 1.9166666667 1.9583333333 . Unqualified . . 31DEC1985 5786.875
10000 1986-01-15 3680 1 2.875 2.5 2.875 . Unqualified . . 31DEC1985 10580
10001 1986-01-15 985 3 2.0208333333 1.9166666667 2.0208333333 . Unqualified . . 31DEC1985 5971.5625
10000 1986-01-16 3680 1 3 2.5 3 . Unqualified . . 31DEC1985 11040
10001 1986-01-16 985 3 2.0833333333 1.9166666667 2.0833333333 . Unqualified . . 31DEC1985 6156.25
10000 1986-01-17 3680 1 3 2.5 3 . Unqualified . . 31DEC1985 11040
10001 1986-01-17 985 3 2.0833333333 1.9166666667 2.0833333333 . Unqualified . . 31DEC1985 6156.25
10000 1986-01-20 3680 1 3 2.5 3 . Unqualified . . 31DEC1985 11040
10001 1986-01-20 985 3 2.1041666667 1.9166666667 2.1041666667 . Unqualified . . 31DEC1985 6217.8125
10000 1986-01-21 3680 1 3 2.5 3 . Unqualified . . 31DEC1985 11040
10001 1986-01-21 985 3 2.1041666667 1.9166666667 2.0625 . Unqualified . . 31DEC1985 6094.6875
10000 1986-01-22 3680 1 3 2.5 3 . Unqualified . . 31DEC1985 11040
10001 1986-01-22 985 3 2.1041666667 1.9166666667 2.0625 . Unqualified . . 31DEC1985 6094.6875
10000 1986-01-23 3680 1 3.75 2.5 3.75 . Unqualified . . 31DEC1985 13800
10001 1986-01-23 985 3 2.1041666667 1.9166666667 2.0625 . Unqualified . . 31DEC1985 6094.6875
10000 1986-01-24 3680 1 4.1875 2.5 4.1875 . Unqualified . . 31DEC1985 15410
10001 1986-01-24 985 3 2.1041666667 1.9166666667 2.0625 . Unqualified . . 31DEC1985 6094.6875
10000 1986-01-27 3680 1 4.4375 2.5 4.4375 . Unqualified . . 31DEC1985 16330
10001 1986-01-27 985 3 2.1041666667 1.9166666667 2.0625 . Unqualified . . 31DEC1985 6094.6875
10000 1986-01-28 3680 1 4.4375 2.5 4.4375 . Unqualified . . 31DEC1985 16330
10001 1986-01-28 985 3 2.1041666667 1.9166666667 2.0416666667 . Unqualified . . 31DEC1985 6033.125
10000 1986-01-29 3680 1 4.4375 2.5 4.3125 . Unqualified . . 31DEC1985 15870
10001 1986-01-29 985 3 2.1041666667 1.9166666667 2.0208333333 . Unqualified . . 31DEC1985 5971.5625
10000 1986-01-30 3680 1 4.4375 2.5 4.4375 . Unqualified . . 31DEC1985 16330
10001 1986-01-30 985 3 2.1041666667 1.9166666667 2.0208333333 . Unqualified . . 31DEC1985 5971.5625
10000 1986-01-31 3680 1 4.4375 2.5 4.375 . Unqualified . . 31DEC1985 16100
10001 1986-01-31 985 3 2.1041666667 1.9166666667 2.0416666667 . Unqualified . . 31DEC1985 6033.125
10000 1986-02-03 3680 1 4.4375 2.5 4.375 . Unqualified . . 31JAN1986 16100
10001 1986-02-03 985 3 2.1041666667 1.9166666667 2.0416666667 . Unqualified . . 31JAN1986 6033.125
10000 1986-02-04 3680 1 4.4375 2.5 4.375 . Unqualified . . 31JAN1986 16100
10001 1986-02-04 985 3 2.1041666667 1.9166666667 2.0416666667 . Unqualified . . 31JAN1986 6033.125
10000 1986-02-05 3680 1 4.4375 2.5 4.375 . Unqualified . . 31JAN1986 16100
10001 1986-02-05 985 3 2.1041666667 1.9166666667 2.0416666667 . Unqualified . . 31JAN1986 6033.125
10000 1986-02-06 3680 1 4.4375 2.5 4.1875 . Unqualified . . 31JAN1986 15410
10001 1986-02-06 985 3 2.1041666667 1.9166666667 2.0416666667 . Unqualified . . 31JAN1986 6033.125
10000 1986-02-07 3680 1 4.4375 2.5 4.375 . Unqualified . . 31JAN1986 16100
10001 1986-02-07 985 3 2.1041666667 1.9166666667 2.0416666667 . Unqualified . . 31JAN1986 6033.125
10000 1986-02-10 3680 1 4.4375 2.5 4.3125 . Unqualified . . 31JAN1986 15870
10001 1986-02-10 985 3 2.1041666667 1.9166666667 2.0625 . Unqualified . . 31JAN1986 6094.6875
10000 1986-02-11 3680 1 4.4375 2.5 4.3125 . Unqualified . . 31JAN1986 15870
10001 1986-02-11 985 3 2.1041666667 1.9166666667 2.1041666667 . Unqualified . . 31JAN1986 6217.8125

Re: categorize market values into three groups labeled as small, mid, and big

ballardw — Sat, 04 May 2024 12:58:50 GMT

What variable holds the value used for the group assignment?

Re: categorize market values into three groups labeled as small, mid, and big

Irenelee — Sat, 04 May 2024 14:33:42 GMT

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