thx!
How to create a column which is market value of last month
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;
RETAIN
LMV 0;
IF FIRST.PERMNO THEN LMV = MV;
IF LAG(DATE)=Last_Month_End THEN LMV = LAG(MV);
ELSE LMV = LAG(MV);
RUN;
DATA L MV;
SET LMV(OBS=500);
PUT (_ALL_) (+0);
RUN;
It's not as expected now..
10000 1986-01-07 3680 1 2.5625 2.5625 2.5625 Unqualified . . 9430 1985-12-31 .
10000 1986-01-08 3680 1 2.5625 2.5 2.5 Unqualified . . 9200 1985-12-31 9430
10000 1986-01-09 3680 1 2.5625 2.5 2.5 Unqualified . . 9200 1985-12-31 9200
10000 1986-01-10 3680 1 2.5625 2.5 2.5 Unqualified . . 9200 1985-12-31 9200
10000 1986-01-13 3680 1 2.625 2.5 2.625 Unqualified . . 9660 1985-12-31 9200
10000 1986-01-14 3680 1 2.75 2.5 2.75 Unqualified . . 10120 1985-12-31 9660
10000 1986-01-15 3680 1 2.875 2.5 2.875 Unqualified . . 10580 1985-12-31 10120
10000 1986-01-16 3680 1 3 2.5 3 Unqualified . . 11040 1985-12-31 10580
10000 1986-01-17 3680 1 3 2.5 3 Unqualified . . 11040 1985-12-31 11040
10000 1986-01-20 3680 1 3 2.5 3 Unqualified . . 11040 1985-12-31 11040
10000 1986-01-21 3680 1 3 2.5 3 Unqualified . . 11040 1985-12-31 11040
10000 1986-01-22 3680 1 3 2.5 3 Unqualified . . 11040 1985-12-31 11040
10000 1986-01-23 3680 1 3.75 2.5 3.75 Unqualified . . 13800 1985-12-31 11040
10000 1986-01-24 3680 1 4.1875 2.5 4.1875 Unqualified . . 15410 1985-12-31 13800
10000 1986-01-27 3680 1 4.4375 2.5 4.4375 Unqualified . . 16330 1985-12-31 15410
10000 1986-01-28 3680 1 4.4375 2.5 4.4375 Unqualified . . 16330 1985-12-31 16330
10000 1986-01-29 3680 1 4.4375 2.5 4.3125 Unqualified . . 15870 1985-12-31 16330
10000 1986-01-30 3680 1 4.4375 2.5 4.4375 Unqualified . . 16330 1985-12-31 15870
10000 1986-01-31 3680 1 4.4375 2.5 4.375 Unqualified . . 16100 1985-12-31 16330
10000 1986-02-03 3680 1 4.4375 2.5 4.375 Unqualified . . 16100 1986-01-31 .
10000 1986-02-04 3680 1 4.4375 2.5 4.375 Unqualified . . 16100 1986-01-31 16100
10000 1986-02-05 3680 1 4.4375 2.5 4.375 Unqualified . . 16100 1986-01-31 16100
10000 1986-02-06 3680 1 4.4375 2.5 4.1875 Unqualified . . 15410 1986-01-31 16100
10000 1986-02-07 3680 1 4.4375 2.5 4.375 Unqualified . . 16100 1986-01-31 15410
10000 1986-02-10 3680 1 4.4375 2.5 4.3125 Unqualified . . 15870 1986-01-31 16100
10000 1986-02-11 3680 1 4.4375 2.5 4.3125 Unqualified . . 15870 1986-01-31 15870
10000 1986-02-12 3680 1 4.4375 2.5 4.21875 Unqualified . . 15525 1986-01-31 15870
10000 1986-02-13 3680 1 4.4375 2.5 4.21875 Unqualified . . 15525 1986-01-31 15525
10000 1986-02-14 3680 1 4.4375 2.5 4.28125 Unqualified . . 15755 1986-01-31 15525
10000 1986-02-18 3680 1 4.4375 2.5 4 Unqualified . . 14720 1986-01-31 15755
10000 1986-02-19 3680 1 4.4375 2.5 3.9375 Unqualified . . 14490 1986-01-31 14720
10000 1986-02-20 3680 1 4.4375 2.5 3.6875 Unqualified . . 13570 1986-01-31 14490
10000 1986-02-21 3680 1 4.4375 2.5 3.6875 Unqualified . . 13570 1986-01-31 13570
10000 1986-02-24 3680 1 4.4375 2.5 3.625 Unqualified . . 13340 1986-01-31 13570
10000 1986-02-25 3680 1 4.4375 2.5 3.5625 Unqualified . . 13110 1986-01-31 13340
10000 1986-02-26 3680 1 4.4375 2.5 3.25 Unqualified . . 11960 1986-01-31 13110
10000 1986-02-27 3680 1 4.4375 2.5 3.25 Unqualified . . 11960 1986-01-31 11960
10000 1986-02-28 3680 1 4.4375 2.5 3.25 Unqualified . . 11960 1986-01-31 11960
10000 1986-03-03 3680 1 4.4375 2.5 3.25 Unqualified . . 11960 1986-02-28 16100
10000 1986-03-04 3680 1 4.4375 2.5 3.4375 Unqualified . . 12650 1986-02-28 11960
https://drive.google.com/file/d/1grx_6p-yVMVzrzvVjdtfwyFUjRpq10W6/view?usp=sharing
Can you just simplify to the question you are asking?
So let's assume you have dataset with some type of ID variable (looks like perhaps PERMNO is that variable in your case?) some type of DATE variable and some type of numeric VALUE variable. You want to make a NEW variable that has the value from the previous end of month.
So use the LAG() function to get the previous OBSERVATION's value. Then when you are at the first date of a new month save that lagged value.
data want;
set have ;
by permno date ;
lag_value = lag(value);
if first.permno then call missing(lag_value,previous_value);
else if month(date) ne month(lag(date)) then previous_value=lag_value;
retain previous_value;
drop lag_value;
run;
Provide further explanation. What is wrong, specifically, and what would you like to see instead.
I want the FIRMS market value everyday BASED ON THEIR MARKET VALUE by PREVIOUS MONTH’S end.
for the first month, the market value would be everyday's market value.
But after the second month, the base market value(LMV) every day should be the same as the MV of PREVIOUS MONTH’S end (not change everyday, but same as end of last month).
the following is not as expected
So, what is expected?
the FIRMS market value everyday BASED ON THEIR MARKET VALUE by PREVIOUS MONTH’S end.
for the first month, the market value would be everyday's market value.
But after the second month, the base market value(LMV) every day should be the same as the MV of PREVIOUS MONTH’S end (not change everyday, but same as end of last month).
For each permno you want LMV, defined as
This should work:
DATA LMV (drop=_: nxt_:) ;
SET MV (keep=permno);
BY PERMNO;
merge mv
mv (firstobs=2 keep=date rename=(date=nxt_date)) ;
RETAIN LMV .;
if first.permno then _monthnum=1;
if _monthnum=1 then lmv=mv;
output;
/* if this is the last trading date of the month, prepare for next month */
if intnx('month',date,nxt_date)>0 then do;
_monthnum+1;
lmv=mv;
end;
RUN;
The initial code (below) would consistently, just for the last date of each month, mistakenly use the current MV for the LMV. That has to be delayed by one date, as above.
DATA LMV (drop=_: nxt_:) ;
SET MV (keep=permno);
BY PERMNO;
merge mv
mv (firstobs=2 keep=date rename=(date=nxt_date)) ;
RETAIN LMV 0;
if first.permno then _monthnum=1;
if _monthnum=1 or intck('month',date,nxt_date)>0 then lmv=mv; /* If first month, or end-of-current month*/
if intnx('month',lag(date),date)>0 then _monthnum+1; /*Starting a new month */
RUN;
Can you just simplify to the question you are asking?
So let's assume you have dataset with some type of ID variable (looks like perhaps PERMNO is that variable in your case?) some type of DATE variable and some type of numeric VALUE variable. You want to make a NEW variable that has the value from the previous end of month.
So use the LAG() function to get the previous OBSERVATION's value. Then when you are at the first date of a new month save that lagged value.
data want;
set have ;
by permno date ;
lag_value = lag(value);
if first.permno then call missing(lag_value,previous_value);
else if month(date) ne month(lag(date)) then previous_value=lag_value;
retain previous_value;
drop lag_value;
run;
Yes! You are so brilliant!!!
10000 1986-01-07 3680 1 2.5625 2.5625 2.5625 UNQUALIFIED . . 9430 1985-12-31 .
10000 1986-01-08 3680 1 2.5625 2.5 2.5 UNQUALIFIED . . 9200 1985-12-31 9430
10000 1986-01-09 3680 1 2.5625 2.5 2.5 UNQUALIFIED . . 9200 1985-12-31 9430
10000 1986-01-10 3680 1 2.5625 2.5 2.5 UNQUALIFIED . . 9200 1985-12-31 9430
10000 1986-01-13 3680 1 2.625 2.5 2.625 UNQUALIFIED . . 9660 1985-12-31 9430
10000 1986-01-14 3680 1 2.75 2.5 2.75 UNQUALIFIED . . 10120 1985-12-31 9430
10000 1986-01-15 3680 1 2.875 2.5 2.875 UNQUALIFIED . . 10580 1985-12-31 9430
10000 1986-01-16 3680 1 3 2.5 3 UNQUALIFIED . . 11040 1985-12-31 9430
10000 1986-01-17 3680 1 3 2.5 3 UNQUALIFIED . . 11040 1985-12-31 9430
10000 1986-01-20 3680 1 3 2.5 3 UNQUALIFIED . . 11040 1985-12-31 9430
10000 1986-01-21 3680 1 3 2.5 3 UNQUALIFIED . . 11040 1985-12-31 9430
10000 1986-01-22 3680 1 3 2.5 3 UNQUALIFIED . . 11040 1985-12-31 9430
10000 1986-01-23 3680 1 3.75 2.5 3.75 UNQUALIFIED . . 13800 1985-12-31 9430
10000 1986-01-24 3680 1 4.1875 2.5 4.1875 UNQUALIFIED . . 15410 1985-12-31 9430
10000 1986-01-27 3680 1 4.4375 2.5 4.4375 UNQUALIFIED . . 16330 1985-12-31 9430
10000 1986-01-28 3680 1 4.4375 2.5 4.4375 UNQUALIFIED . . 16330 1985-12-31 9430
10000 1986-01-29 3680 1 4.4375 2.5 4.3125 UNQUALIFIED . . 15870 1985-12-31 9430
10000 1986-01-30 3680 1 4.4375 2.5 4.4375 UNQUALIFIED . . 16330 1985-12-31 9430
10000 1986-01-31 3680 1 4.4375 2.5 4.375 UNQUALIFIED . . 16100 1985-12-31 9430
10000 1986-02-03 3680 1 4.4375 2.5 4.375 UNQUALIFIED . . 16100 1986-01-31 16100
10000 1986-02-04 3680 1 4.4375 2.5 4.375 UNQUALIFIED . . 16100 1986-01-31 16100
10000 1986-02-05 3680 1 4.4375 2.5 4.375 UNQUALIFIED . . 16100 1986-01-31 16100
10000 1986-02-06 3680 1 4.4375 2.5 4.1875 UNQUALIFIED . . 15410 1986-01-31 16100
10000 1986-02-07 3680 1 4.4375 2.5 4.375 UNQUALIFIED . . 16100 1986-01-31 16100
10000 1986-02-10 3680 1 4.4375 2.5 4.3125 UNQUALIFIED . . 15870 1986-01-31 16100
10000 1986-02-11 3680 1 4.4375 2.5 4.3125 UNQUALIFIED . . 15870 1986-01-31 16100
10000 1986-02-12 3680 1 4.4375 2.5 4.21875 UNQUALIFIED . . 15525 1986-01-31 16100
10000 1986-02-13 3680 1 4.4375 2.5 4.21875 UNQUALIFIED . . 15525 1986-01-31 16100
10000 1986-02-14 3680 1 4.4375 2.5 4.28125 UNQUALIFIED . . 15755 1986-01-31 16100
10000 1986-02-18 3680 1 4.4375 2.5 4 UNQUALIFIED . . 14720 1986-01-31 16100
10000 1986-02-19 3680 1 4.4375 2.5 3.9375 UNQUALIFIED . . 14490 1986-01-31 16100
10000 1986-02-20 3680 1 4.4375 2.5 3.6875 UNQUALIFIED . . 13570 1986-01-31 16100
10000 1986-02-21 3680 1 4.4375 2.5 3.6875 UNQUALIFIED . . 13570 1986-01-31 16100
10000 1986-02-24 3680 1 4.4375 2.5 3.625 UNQUALIFIED . . 13340 1986-01-31 16100
10000 1986-02-25 3680 1 4.4375 2.5 3.5625 UNQUALIFIED . . 13110 1986-01-31 16100
10000 1986-02-26 3680 1 4.4375 2.5 3.25 UNQUALIFIED . . 11960 1986-01-31 16100
10000 1986-02-27 3680 1 4.4375 2.5 3.25 UNQUALIFIED . . 11960 1986-01-31 16100
10000 1986-02-28 3680 1 4.4375 2.5 3.25 UNQUALIFIED . . 11960 1986-01-31 16100
10000 1986-03-03 3680 1 4.4375 2.5 3.25 UNQUALIFIED . . 11960 1986-02-28 11960
10000 1986-03-04 3680 1 4.4375 2.5 3.4375 UNQUALIFIED . . 12650 1986-02-28 11960
10000 1986-03-05 3680 1 4.4375 2.5 3.46875 UNQUALIFIED . . 12765 1986-02-28 11960
10000 1986-03-06 3680 1 4.4375 2.5 3.875 UNQUALIFIED . . 14260 1986-02-28 11960
10000 1986-03-07 3680 1 4.4375 2.5 4.4375 UNQUALIFIED . . 16330 1986-02-28 11960
10000 1986-03-10 3680 1 4.4375 2.5 4.1875 UNQUALIFIED . . 15410 1986-02-28 11960
I want to create a column "LMV" for each PERMNO everyday which is its market value of end of last month.(make a NEW variable that has the value from the previous end of month. )
I have dataset with some type of ID variable (PERMNO is the variable in my case)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.