turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- Using previous observation and calculating to get ...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-01-2014 05:32 AM

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:

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Bruce123

12-01-2014 05:55 AM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-01-2014 06:10 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Bruce123

12-01-2014 06:15 AM

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-01-2014 06:59 AM

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;

If Month=Lag(Month) Then

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

Else

MTD_Test = DTD;

In excel, it would look like this:

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Bruce123

12-01-2014 09:12 AM

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-01-2014 10:01 AM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Bruce123

12-01-2014 10:13 AM

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-01-2014 10:33 AM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Bruce123

12-01-2014 10:12 AM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Astounding

12-01-2014 10:37 AM

Hello

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Bruce123

12-01-2014 11:14 AM

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.