Using previous observation and calculating to get new current observatoin

Reply
Contributor
Posts: 47

Using previous observation and calculating to get new current observatoin

Hello

I am trying to calculate the value of a new variable based on its previous value (where its first value is given).

My data looks like this:

Image.PNG

The variable I am working with is "MTD". So, for the time, when months are not equal to each other there is a value given.

But for the rest, where the months are equal to eachother, I would like to use a formula to calculate the values using the previous value of "MTD";

My code is as follows:

Data Want;

Set Have;

  If Month=Lag(Month) Then

  MTD = (1+DTD)*(1+Lag(MTD))-1;

  Else

  MTD = DTD;

But it seems I can't use the "Lag(MTD)". I get wrong values for the variable.

Any suggestion to how this problem can be solved?

All help will be greatly appreciated.

Best Regards

Bruce

Super User
Super User
Posts: 7,668

Re: Using previous observation and calculating to get new current observatoin

Hi,


You don't actually need lag for that, retain will do it:

data have;

  month=1; mtd="ABC"; output;

  month=1; mtd=""; output;

  month=2; mtd="BCD"; output;

  month=2; mtd=""; output;

run;

data want;

  set have;

  retain mtd_filled;

  if _n_=1 or mtd ne "" then mtd_filled=mtd;

run;

Note, for lag, you will want to put it into a new variable lag into same variable will not work.

Contributor
Posts: 47

Re: Using previous observation and calculating to get new current observatoin

Hello

Thanks for your replay.

I have data for all 12 months and afterwards I am going to the day just for weeks.

So, I was wondering if there isn't a way, where I can just write a formula combined with an "If-statement" to solve this problem?

Is this possible?

Regards

Bruce

Super User
Super User
Posts: 7,668

Re: Using previous observation and calculating to get new current observatoin

Then you would need new variables:

so instead of setting mtd:

  If Month=Lag(Month) Then

  new_var = (1+DTD)*(1+Lag(MTD))-1;

  Else

  new_var = DTD;

Contributor
Posts: 47

Re: Using previous observation and calculating to get new current observatoin

I tried this and the problem is a bit like my orginal code. The problem beting that at this time (where I try to insert your code) I do not have values for MTD when the months are equal to each other. Because when the months are equal to each, I would like to use the "new" calculated lag MTD.

I hope the above makes sense.

Below I posted the code i tried and the output I got. I've done this in excel aswell, where it took a screenshot of it - hopefully it can help show what it is I wish to do.

Sas Output;

Image.PNG

If Month=Lag(Month) Then

  MTD_Test = (1+DTD)*(1+Lag(MTD))-1;

  Else

  MTD_Test = DTD;

In excel, it would look like this: Image 2.PNG

Super User
Super User
Posts: 7,668

Re: Using previous observation and calculating to get new current observatoin

Sorry, now I am really confused.  Your example Excel output doesn't match the data you have, why is the second row purple different from the test data?

What I see as your problem is:

1) You want to have MTD value from first row per group copied down to each row - > use retain as given.

2) You then want to calculate a new variable based on this now populated data.

So:

data want;

  set have;

  retain mtd_filled;

  if _n_=1 or mtd ne "" then mtd_filled=mtd;

run;

The above solves (1).

You can then use this dataset with your formula:

data want2;

     set want;

  If Month=Lag(Month) Then

  new_var = (1+DTD)*(1+MTD_filled)-1;

  Else

  new_var = DTD;

run;

Contributor
Posts: 47

Re: Using previous observation and calculating to get new current observatoin

The rows are different, because I quickly deleted a few rows to make the output smaller, so it would be easiler to read - sorry I did not state this.

The values you see in the "MTD" are fine (from the output at the top of the post). What I basically want to do from here is fill in the empty spots with the formula: (1+DTD)*(1+Lag(MTD))-1, but, as you stated, SAS does not allow this (this is the point where I think we misunderstood eachother.).

So, in numbers; row 3 would look like this: (1+0,004)*(1+0,005)-1 = x    (where 0,004 is DTD, and 0,005 is last periods MTD)

Row 4 would be: (1+-0,005)*(1+x)-1

And so on.

Super User
Super User
Posts: 7,668

Re: Using previous observation and calculating to get new current observatoin

Something like:

data have;

  dtd=0.00502; month=1; weekday=2; mtd=0.00502; output;

  dtd=0.00422; month=1; weekday=4; mtd=.; output;

  dtd=0.00502; month=1; weekday=5; mtd=.; output;

  dtd=0.00502; month=1; weekday=1; mtd=.; output;

run;

data want;

  set have;

  retain last_mtd;

  by month;

  if first.month then last_mtd=mtd;

  else last_mtd=(1+dtd) * (1+last_mtd) - 1;

run;

Contributor
Posts: 47

Re: Using previous observation and calculating to get new current observatoin

For the 2nd and 3rd row (if you include "data have" as a row) of your first code, yes.

For the 4. and 5. row of your first code, no. The values here should be the values of "DTD" in rows 4 and 5; So, -0,005177 and 0,000413.

However, since I have a lot of observations I would prefer to write a formula or command so SAS will do this rather than having to writting each observation. Also since the data will change next year, then I will have to writ it in again if I do not writ a formula or command.

I am sorry for the troubling I am causing you, but I do appreciate you useing your time to help me.

Super User
Posts: 5,351

Re: Using previous observation and calculating to get new current observatoin

The LAG function is trickier than it seems.  You will need to calculate lag(MTD) for every observation to get your logic to work.  Assuming that the logic is otherwise correct:

prior_MTD = lag(MTD);

If Month=Lag(Month) Then

new_var = (1+DTD)*(1+prior_MTD)-1;

drop prior_MTD;

Good luck.

Contributor
Posts: 47

Re: Using previous observation and calculating to get new current observatoin

Hello

Is there a way arround so I do not have to calculate Lag(MTD) for each?

Super User
Posts: 5,351

Re: Using previous observation and calculating to get new current observatoin

You could use this instead of the LAG function, if you are more comfortable with it:

data want;

   prior_mtd = mtd;

   set have;

Then you would have prior_MTD without using the LAG function.  But it still gets calculated every time.

Note that this technique works easily for numeric variables.  If you try to use it for a character variable, you would need to add a LENGTH statement first.

Good luck.

Ask a Question
Discussion stats
  • 11 replies
  • 536 views
  • 1 like
  • 3 in conversation