Desktop productivity for business analysts and programmers

Lag of the same variable

Reply
Contributor
Posts: 30

Lag of the same variable

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!

Grand Advisor
Posts: 10,211

Re: Lag of the same variable

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.

 

Contributor
Posts: 30

Re: Lag of the same variable

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
Grand Advisor
Posts: 10,211

Re: Lag of the same variable

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;
Contributor
Posts: 30

Re: Lag of the same variable

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
Grand Advisor
Posts: 10,211

Re: Lag of the same variable

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. 

 

Ask a Question
Discussion stats
  • 5 replies
  • 269 views
  • 1 like
  • 2 in conversation