BookmarkSubscribeRSS Feed
Irenelee
Quartz | Level 8

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.

4 REPLIES 4
Irenelee
Quartz | Level 8

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;
Irenelee
Quartz | Level 8

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

ballardw
Super User

What variable holds the value used for the group assignment?

Irenelee
Quartz | Level 8

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.

 

LMV.png

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 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 318 views
  • 0 likes
  • 2 in conversation