How to create a column which is market value of last month

[Irenelee]

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 

[PaigeMiller]

Provide further explanation. What is wrong, specifically, and what would you like to see instead.

[Irenelee]

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).

[Irenelee]

the following is not as expected

[ballardw]

So, what is expected?

[Irenelee]

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).

[mkeintz]

For each permno you want LMV, defined as

1. current daily MV for the first month
2. for all other month, the MV of the last trading date of the prior month.
   
   

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; 

[Tom · Accepted solution]

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;

[Irenelee]

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)