Lag by variable

[Xinhui]

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. 

[PeterClemmensen]

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 

[Reeza]

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