topic Re: Lag by variable in SAS Programming

Lag by variable

Xinhui — Thu, 21 Nov 2019 05:51:26 GMT

Hi there

I have a variable "Price" and I would like to creat a new variable "P1” which is lag 5 of "price" by variable "ticker" . I used the following code

data full;
set master;
By ticker;
P1=lag5(PRICE);
IF NOT FIRST.Ticker THEN P1= .;
run;

However, the code not doing well.

Plase let me know how to improve.

Thanks in advance.

Re: Lag by variable

PeterClemmensen — Thu, 21 Nov 2019 06:04:15 GMT

I'm guessing that the problem is that your code does not handle by groups well?

If so. There are several ways to handle this. In the data step, you could do something like this

data have(drop=i);
   do ticker='a', 'b', 'c';
      do i=1 to 10;
         price=rand('integer', 1, 100);
         output;
      end;
   end;
run;

data want(drop=t5);
   set have;
   p5=lag5(price);
   t5=lag5(ticker);
   if t5 ne ticker then call missing(p5);
run;

Result:

ticker price p5 
a      1     . 
a      76    . 
a      6     . 
a      99    . 
a      77    . 
a      59    1 
a      73    76 
a      10    6 
a      91    99 
a      1     77 
b      89    . 
b      4     . 
b      21    . 
b      88    . 
b      26    . 
b      38    89 
b      58    4 
b      95    21 
b      72    88 
b      15    26 
c      46    . 
c      59    . 
c      78    . 
c      84    . 
c      26    . 
c      98    46 
c      16    59 
c      61    78 
c      54    84 
c      38    26

Re: Lag by variable

Reeza — Thu, 21 Nov 2019 06:15:55 GMT

Do you have SAS/ETS? If so, try PROC EXPAND.