Lag of the same variable

[Lopes]

Hi, 

How can I go from this:

ID	Date	Price
a1	08Apr2016	5,0999
a1	11Apr2016	5
a1	12Apr2016	0
a1	13Apr2016	5,2
a1	14Apr2016	0
a1	15Apr2016	0

to this:

ID	Date	Price	Price_2
a1	08Apr2016	5,0999	5,0999
a1	11Apr2016	5	5
a1	12Apr2016	0	5
a1	13Apr2016	5,2	5,2
a1	14Apr2016	0	5,2
a1	15Apr2016	0	5,2

Spoiler

If price=0 I need price_2 to be the last price available as shown in the last table. 

 Thanks!

[ballardw]

Here is one approach if I understand correctly:

data want;
   set have;
   Lprice= Lag(price);
   if Price ne 0 then Price_2=Price;
   else Price = Lprice;
   drop Lprice;
run;

There are some tricks involving LAG and conditional statements that until you spend some time working with lag this is the most reliable way to use Lag.

[Lopes]

Thanks. 

It works correctly for the first zero, if the variable have more than one zero, then the lag will be zero and I need it to be the last available price. As the following example, that the price is 8 for april 5 and as the next days don´t have prices I use that price. 

id	date	price	price_2
a1	05/04/2015	8	8
a1	06/04/2015	0	8
a1	07/04/2015	0	8
a2	08/04/2015	0	8

[ballardw]

Try:

data want;
   set have;
   Retain Lprice;
   if Price ne 0 then do;
      Price_2=Price;
      Lprice =price;
   end;
   else Price_2 = Lprice;
   drop Lprice;
run;

[Lopes]

Thanks!

It works correctly, I have an additional problem. If I have more than one ID and the price is 0 in the first observation it puts the last price of the previous ID. How can I do this?

I need to have:

ID	date	price	price2
a1	04Apr2016	9	9
a1	05Apr2016	0	9
a1	06Apr2016	0	9
a1	07Apr2016	0	9
a1	08Apr2016	8	8
a2	04Apr2016	0	0
a2	05Apr2016	0	0
a2	06Apr2016	7	7
a2	07Apr2016	0	7
a2	08Apr2016	0	7

[ballardw]

If the data is grouped by ID then this should work

data want;
   set have;
   by id notsorted;
   Retain Lprice;
   if first.id then do;
      lprice=price;
      lprice=price;
   end;
   if Price ne 0 then do;
      Price_2=Price;
      Lprice =price;
   end;
   else Price_2 = Lprice;
   drop Lprice;
run;

Note that if you have multiple levels of grouping variables you may be able to use the FIRST. processing (and often Last.) but you'll want to look at the docmenation about order, by statement options (notsorted descending) and pay attention to which values are changing.