topic Re: Lag of the same variable in SAS Enterprise Guide

Lag of the same variable

Lopes — Thu, 26 May 2016 20:32:47 GMT

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

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

Thanks!

Re: Lag of the same variable

ballardw — Thu, 26 May 2016 21:25:19 GMT

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.

Re: Lag of the same variable

Lopes — Thu, 26 May 2016 21:34:21 GMT

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

Re: Lag of the same variable

ballardw — Thu, 26 May 2016 22:09:11 GMT

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;

Re: Lag of the same variable

Lopes — Fri, 27 May 2016 13:15:17 GMT

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

Re: Lag of the same variable

ballardw — Fri, 27 May 2016 15:12:22 GMT

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.