BookmarkSubscribeRSS Feed
Lopes
Calcite | Level 5

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!

5 REPLIES 5
ballardw
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Super User

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. 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1206 views
  • 1 like
  • 2 in conversation