05-26-2016 04:32 PM
How can I go from this:
If price=0 I need price_2 to be the last price available as shown in the last table.
05-26-2016 05:25 PM
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.
05-26-2016 05:34 PM
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.
05-27-2016 09:15 AM
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:
05-27-2016 11:12 AM
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.