Recently in the SAS Community Library: SAS' @StuartRogers provides a close look at the new Microsoft Entra Gallery application and details how it can be used.
How may I CALCULATE THE EQUAL-WEIGHTED (EW) AND VALUE-WEIGHTED (VW) COMPOUND DAILY RETURN FOR THE SUBSEQUENT WEEK AND MONTH BEGINNING THE DAY AFTER A FIRM IS ASSIGNED TO THE MAX, MIN, OR COMPARISON PORTFOLIO BY SMALL, MEDIUM, BIG SIZE. Thx a lot!!!
AVERAGE DAILY PORTFOLIO BUY-AND-HOLD RETURNS (IN %) ARE MEASURED FOR THE SUBSEQUENT WEEK AND MONTH BEGINNING THE DAY AFTER A FIRM IS ASSIGNED TO THE MAX, MIN, OR COMPARISON PORTFOLIO.
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;
DATA SMB;
SET SMB(OBS=500);
PUT (_ALL_) (+0);
RUN;
PROC SORT DATA = SORTED;
BY DATE SIZE_GROUP PORTFOLIO;
RUN;
DATA EW_RETURNS VW_RETURNS;
SET SORTED;
BY DATE SIZE_GROUP PORTFOLIO;
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.PORTFOLIO 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;
EQUAL-WEIGHTED (EW) AND VALUE-WEIGHTED (VW) PORTFOLIO RETURNS ARE CALCULATED DAILY, AND THE PORTFOLIO IS REBALANCED AT THE END EACH DAY.
EW RETURNS ARE COMPOUNDED TO REDUCE BID ASK BIAS.
https://drive.google.com/file/d/1grx_6p-yVMVzrzvVjdtfwyFUjRpq10W6/view?usp=drive_link
... View more
Proc export giving additional quotes to the quoted char values.and if u use data step for that it is giving a space or . To numeric values which is not required . Needs to change nothing.now it is , , but want ,, no space no .for missing numeric value.
... View more
Hi, I have a dataset where the year goes from [2000,2015] where I have these countries: Spain, France, Denmark and European Union and let's say two colomns; company='A' and 'B' and value='dollar' Q1: if Spain for company A have average vaule 0.288 Denmark also France also for aaaalll of the 15 years. BUT! We have EU (recall, France, Spain, Denmark are all in European Union (EU)) and for one (1) year EU have the avarage for the same company A, have tha average vaule at 1,000 Q2: For company B;, France, Spain, Denmark and European Union (EU)) have data from [2000,2015] and have ~the same avarage all the four, how to solve that issue besT?
... View more
The fact that I even have to post this tells u what kind of day I'm having 😞 If the HLT_ALZDEM variable doesn't have a valid value of 1 or 2 (yes/no), then the variables that are constructed based on HLT_ALZDEM should be given a value of missing. However, they sometimes are given a value of 0 instead.
Below is a portion of the output file, sorted for rows where HLT_ALZDEM is missing. Since missing is clearly not 1 or 2, all ADRD_group and non_ADRD_group values should also be missing. But they're not. Obs 9 and 10 are identical except for these 2 variables., when Obs 9 is missing and Obs 10 is 0 for both.
The only possible explanation I can think of is that there are non-printable characters for some values of HLT_ALZDEM. But even if there are, those non-printable characters are not 1 or 2. Frequencies for HLT_ALZDEM only show values of 1, 2, and missing.
IF cohort_flag = 1 AND ADM_MA_FLAG_YR = 3
THEN MA_group = 1; ELSE MA_group = 0;
IF cohort_flag = 0 THEN MA_group = .;
IF cohort_flag = 1 AND ADM_FFS_FLAG_YR = 3
THEN TM_group = 1; ELSE TM_group = 0;
IF cohort_flag = 0 THEN TM_group = .;
IF cohort_flag = 1 AND HLT_ALZDEM = 1
THEN ADRD_group = 1; ELSE ADRD_group = 0;
IF cohort_flag = 0 THEN ADRD_group = .;
IF HLT_ALZDEM ^in (1,2) THEN ADRD_group = .;
IF cohort_flag = 1 AND HLT_ALZDEM = 2
THEN non_ADRD_group = 1; ELSE non_ADRD_group = 0;
IF cohort_flag = 0 THEN non_ADRD_group = .;
IF HLT_ALZDEM ^in (1,2) THEN non_ADRD_group = .;
Obs
HLT_ALZDEM
cohort_flag
MA_group
TM_group
ADRD_group
non_ADRD_group
1
0
2
1
0
1
0
0
3
1
1
0
4
0
5
0
6
1
0
1
7
1
1
0
8
1
1
0
9
1
1
0
10
1
1
0
0
0
11
1
1
0
12
1
0
1
13
1
0
1
14
1
0
1
15
1
0
1
16
1
0
1
0
0
... View more
Hi guys,
suppose to have the following:
data DB1;
input ID Index_date Code Admission Discharge Status Date;
format Admission Discharge date9.;
cards;
0001 1 49121 11JAN2018 07FEB2018 Died .
0001 1 4660 11JAN2018 07FEB2018 Died .
0001 0 4821 23MAY2021 21JUN2021 Died .
0002 1 4660 01OCT2017 10OCT2017 Died .
0003 1 4659 30MAY2017 7JUN2017 Died .
0003 0 4659 01JAN2018 10JAN2018 Died .
0004 1 V0182 11NOV2021 17NOV2021 Died .
0004 1 V0182 11NOV2021 17NOV2021 Died .
0004 1 4829 11NOV2021 17NOV2021 Died .
;
data DB2;
input ID Index_date Code Admission Discharge Status Date;
format Admission Discharge date9.;
cards;
0001 1 49121 11JAN2018 07FEB2018 Died 22JUN2021
0001 1 4660 11JAN2018 07FEB2018 Died 22JUN2021
0001 0 4821 23MAY2021 21JUN2021 . .
0002 1 4660 01OCT2017 10OCT2017 Died 11OCT2017
0003 1 4659 30MAY2017 07JUN2017 Died 11JAN2018
0003 0 4659 01JAN2018 10JAN2018 . .
0004 1 V0182 11NOV2021 17NOV2021 Died 18NOV2021
0004 1 V0182 11NOV2021 17NOV2021 Died 18NOV2021
0004 1 4829 11NOV2021 17NOV2021 Died 18NOV2021
;
The desired output id DB2.
I would like to assign the death date as the day after the last recorded discharge date for each ID. It could happen that there is only one Admission-Discharge date for a patient like for ID = 002. Doesn't matter. It could also happen that the Admission-Discharge date is repeated equally (es: ID: 004). This happens because of different recorded codes. Doesn't matter. The death date should be the first day after the last (and repeated) discharge date. Patients are sorted by ID and Admission date. Note that there is also an Index_date that indicate the first admission for that patient.
Finally the format of the table DB2 should be changed with respect to DB1. The death date and the word "Died" should be added to the row where Index_date = 1.
Can anyone help me please?
Thank you in advance
... View more