How to calculate 48 sets of returns

[Irenelee]

Would u help me to calculate 48 sets of returns (2*3*2*2*2)

• 2 breakouts portfolio (Max vs min) <I have done the column attached>

• 3 sizes (Small vs Mid vs Big)< I have done the column attached>
• 2 weighted methods (value-weighted vs equal-weighted)
• 2 groups of stocks (all stocks vs stocks that have broken out inportfolio Max or Min  ) 
  *2 Return type (one-week or one-month)

NOW I HAVE

sample Data
https://docs.google.com/spreadsheets/d/1Jr2ophd_Q_MJqO72VO0PU17cyVlSPNK2/edit?usp=sharing&ouid=10104...

• Portfolio: Contains daily breakout (Max, Min, unqualified) flags
• Size: Contains size flags (Small, Mid, Big)
• PERMNO: Unique company code
• DATE: Daily date
• SHROUT: Number of shares
• CFACSHR: Share adjustment factor
• P: Adjusted closing price
• H: Historical high price
• L: Historical low price
• H_DATE: Date of last historical high price
• L_DATE: Date of last historical low price
• MV: Market capitalization
• LAST_MONTH_END: Date at the end of the previous month used to calculate market capitalization
• previous_MV: Market capitalization at the end of the previous month
• QUARTILE: Quartile used to determine which quartile the market capitalization falls into
  • 0: Smallest (market capitalization 0-25%)
  • 1: Medium (market capitalization 25-50%)
  • 2: Large (market capitalization 50-100%)
  • Some stocks have no market capitalization and are classified as "nosize"
• SIZE: Corresponds to the quartiles above
• RET: Daily return: (Today's closing price - Yesterday's closing price) / Yesterday's closing price

Objective

Calculate the one-week and one-month returns (using the RET column) for stocks that break out to a new high or new low each day, weighted by market capitalization and arithmetic average. This results in 24 sets of returns:

2 * 3 * 2 * 2 = 24

More portfolio flag/column explanation

• Max: Stocks that break out to a new high on certain day. However, the company must have been in existence for at least one year to be considered a new high. Stocks that set a new high within two days of their inception are not included in Max.
• Min: Stocks that break out to a new low on the certain day.
• Comparison: Remaining stocks.
• Unqualified: Data from the first year, not included in return calculations, but prices are still used to determine historical highs and lows.

1. Calculate daily market capitalization:

   • MV = P * CFACSHR * SHROUT

2. Assign stocks to quartiles:

   • Use the QUARTILE column to determine which quartile each stock falls into.

3. Calculate weighted and arithmetic average returns for each set of stocks:

   • For each of the 24 sets of stocks (portfolio defined by breakout, size, weighting method, and stock group), calculate the one-week and one-month returns using both market capitalization weighting and arithmetic averaging.

Output

A table with the following category
2 new highs and lows (Max vs Min )portfolio Breakout

*3 sizes (Small vs Mid vs Big)

*2 weighting methods (Market capitalization-weighted return vs equal-weighted)

*2 groups of stocks (all stocks vs those that have reached new highs or lows)

*2 Return type (one-week or one-month)

table may seems like the following

Additional notes

• The calculation should be performed daily to update the returns.
• The results can be used to analyze the performance of different breakout strategies and stock groups.
  
  

  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 RET
                        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=4 TIES=LOW;
    BY DATE;
    VAR previous_MV;
    RANKS QUARTILE;
  RUN;
  
  /*
  proc contents data=RANKED_DATA; 
  run;
  */
  
  
  DATA SMB;
      SET RANKED_DATA; 
  RETAIN 
      SIZE
  	;             
      LENGTH  SIZE $6;
      IF QUARTILE = 0 THEN SIZE = 'SMALL';
      else IF QUARTILE = 1 THEN SIZE = 'MID';
      else IF QUARTILE IN (2,3) THEN SIZE ='BIG';
      ELSE SIZE ='NOSIZE';
  RUN;
  
  
  
  data filtered_data;
      set SMB;
      where date between '24OCT1979'd and '01NOV1979'd;
  run;
  
  PROC EXPORT DATA= WORK.FILTERED_DATA 
              OUTFILE= "C:\Users\alain\OneDrive\桌面\check.xls" 
              DBMS=EXCEL REPLACE;
       SHEET="check"; 
  RUN;
  
  
  /*
  DATA See;
    SET SMB(OBS=500);
    PUT (_ALL_) (+0);
  RUN;
  
  
  
  
  
  

  
  https://drive.google.com/file/d/1grx_6p-yVMVzrzvVjdtfwyFUjRpq10W6/view?usp=drive_link 

[Patrick]

@Irenelee 

Thank you for providing a lot of information including your current code and data upfront. What normally works best in the forums here is asking for help for targeted challenges. I feel your request is a bit too broad - but who knows, may be someone in your field has already done something similar and is willing to share actual code or at least give you pointers how to proceed.

You would likely get more people helping if:

1. Share less data. Just a representative sample that's sufficient for the problem.

2. Explain in as much detail as you can the logic required to get from your sample data to the desired result.

3. Show the desired result based on your sample data. This allows us to test the code we propose.

A lot of people won't download data shared via a link. The attached create_data.sas file is data step code that creates a table Have with the source data you shared via link.