Hello,   I have a dataset that I need to calculate the rolling average and sum.  The file is attached.  What I intend to do here is to calculate rolling average/sum of variables from -380 days to -20 days (I also require at least 50 non missing observations). The code is below. However, it seems that there are some very large numbers in the output files, starting from around row 800.  The ouput number is not consistent with my manual calculation. I guess it might be due to the missing observations.  I would like to know what I missed here.  Any help is appreciated.   proc expand data = test2 out = test2; by permno; id date; convert logret = logret_new/transformout=(lag 20 movsum 360 trim 70); convert turnover = turnover_new/transformout=(lag 20 movave 360 trim 70); convert ret = std /transformout=(lag 20 movstd 360 trim 70); run; ... View more

I want to Apply Tests for Special Causes in the p-chart, but the sample size for each group is different. So, I don’t have a fixed UCL and LCL, and I will get the following warning with below code: WARNING: Asymmetric control limits encountered for yes_answer1 for at least one subgroup. proc shewhart data=tmp2; pchart yes_answer1*quarter / subgroupn = total_count1 tests =1 to 8 TESTNMETHOD=STANDARDIZE table tablelegend; run; Note: The SHEWHART procedure provides an option for working with unequal subgroup sample sizes. For example, I can use the LIMITN= option to specify a fixed (nominal) sample size for computing the control limits. Below is the sample size for each group that I have; my question is, how can I choose the number for LIMITN? quarter   Subgroup 2021Q3    30 2021Q4    66 2022Q1    54 2022Q2    66 2022Q3    69 2022Q4    74 2023Q1    83 2023Q2    96     ... View more

  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=101047452363876489490&rtpof=true&sd=true   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. Calculate daily market capitalization: MV = P * CFACSHR * SHROUT Assign stocks to quartiles: Use the QUARTILE column to determine which quartile each stock falls into. 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    ... View more

  with the following code, the chart displays a label " p-chart for yes_answer1" at the top, under the main chart title. I have two questions about that: How to remove that, i.e "p-chart for yes_answer1" ?  How can I modify the chart so that the main title " P-chart of Patient Counseling by Quarter" appears at the top of the chart instead of "p-chart for yes_answer1" title "P-chart of Patient Counseling by Quarter"; proc shewhart data=tmp2; pchart yes_answer1*quarter/ markers  subgroupn = total_count1  nohlabel ; LABEL yes_answer1 ="Proportion of pts counselled"; /*this would be for y axis and no issue with this*/ run; title; ... View more

I need to add a horizontal reference line at 90% to represent my target in the p-chart. When I put  href=0.9 in my code I got the following warning "WARNING: Numeric href= values are incompatible with a character subgroup variable; HREF= lines are not displayed." The warning indicates that the href value is not compatible because the subgroup variable, quarter, is treated as a character data type. How to include the target line?   Here is my code: proc shewhart data=tmp2; pchart yes_answer1*quarter/ markers  subgroupn = total_count1 nohlabel   Href=0.9; run; ... View more