topic Re: Help with LAG or RETAIN? in SAS Procedures

Help with LAG or RETAIN?

iiibbb — Mon, 06 Feb 2012 19:30:25 GMT

I have a time dataset. I have 4 columns in this dataset that identify the zone a machine occupies as it moves from one place to another.

I want to create a field that increases by 1 whenever the data being collected changes from one state to another -- essentially I want to assign leg numbers, with a new leg beginning anytime it changes from one zone to another.

I thought I could do this using the LAG function with some if/then statements, but I'm apparently doing something wrong.

Now I wonder if the RETAIN might be what I need to use.

Any help would be appreciated.

Re: Help with LAG or RETAIN?

MikeZdeb — Mon, 06 Feb 2012 19:38:00 GMT

hi ... so is this supposed to start at 1, then 2, etc.

data x;

input zone @@;

datalines;

66 66 66 77 77 77 77 888 888 9999 9999

;

run;

data x;

set x;

by zone;

leg + first.zone

run;

zone leg

66 1

77 2

888 3

9999 4

Re: Help with LAG or RETAIN?

iiibbb — Mon, 06 Feb 2012 19:44:53 GMT

That almost works except sometimes the machine re-enters a zone, but on a new leg.

And yes, the start leg is leg 1.

Re: Help with LAG or RETAIN?

iiibbb — Mon, 06 Feb 2012 19:48:58 GMT

the practice dataset looks like this

UTC_Seconds	Landing	HL1	Field	HL2	Speed	Dummy
10001	1	0	0	0	1	10000001
10002	1	0	0	0	1	10000002
10003	1	0	0	0	1	10000003
10004	1	0	0	0	1	10000004
10005	1	0	0	0	1	10000005
10006	1	0	0	0	1	10000006
10007	1	0	0	0	1	10000007
10008	1	0	0	0	2	10000008
10009	1	0	0	0	2	10000009

But in part of the code I do assign a single variable to describe location

data a;

set timestd.practice;

run;

/*segmentation and control points*/

data b;

set a;

if landing = 1 then Leg_Type = 'LANDING';

if HL1 = 1 then Leg_Type = 'HL1';

if Field = 1 then Leg_Type = 'FIELD';

if HL2 = 1 then Leg_Type = 'HL2';

if HL2 + Field + HL2 + Landing = 0 then Leg_Type = 'TRANS';

run;

data c;

set b;

/* these statements want me to sort the data by leg_type */

by leg_type;

leg + first.legtype;

proc print;

run;

Re: Help with LAG or RETAIN?

iiibbb — Mon, 06 Feb 2012 20:34:12 GMT

I'm still tinkering with it, but It appears that if I use the line

if Leg_Type = Lag1(Leg_Type) then CP=0; else CP=1;

I can create a dataset that looks the same as if I had used a first. variable in the datastep processing... based on this website's information,

http://www.pauldickman.com/teaching/sas/set_by.php

but I don't have the groups.

How can I reverse this to create the groups?

Re: Help with LAG or RETAIN?

art297 — Mon, 06 Feb 2012 20:43:16 GMT

You don't have to reverse it so much as change what you want to do when the condition is met. I think you are asking how one does something like:

if Leg_Type ne Lag1(Leg_Type) then group+1;

Re: Help with LAG or RETAIN?

iiibbb — Mon, 06 Feb 2012 20:49:38 GMT

That's what I thought would work. But that yields the same result as

if Leg_Type = Lag1(Leg_Type) then CP=0; else CP=1;

Which is still useful to me because it identifies my control points (the first point of every leg).

However, with

if Leg_Type ne Lag1(Leg_Type) then leg+1;

I get 0's except when I change type, I get a single 1, and then it goes back to zeros.

Re: Help with LAG or RETAIN?

art297 — Mon, 06 Feb 2012 21:06:27 GMT

I may not understand what you are trying to do but, using a slight extension of Mike's test data, does the following do what you want?:

data x;

input leg_type @@;

datalines;

66 66 66 77 77 77 77 66 66 888 888 77 77 77 9999 9999

;

run;

data want;

set x;

by leg_type notsorted;

leg + first.leg_type;

run;

Re: Help with LAG or RETAIN?

iiibbb — Mon, 06 Feb 2012 21:17:29 GMT

Thank you both!!!

Re: Help with LAG or RETAIN?

iiibbb — Tue, 07 Feb 2012 02:48:13 GMT

FYI

I just learned statements can not be used (reliably) in conditional statements.

http://www.nesug.org/proceedings/nesug06/cc/cc32.pdf

Re: Help with LAG or RETAIN?

art297 — Tue, 07 Feb 2012 03:42:05 GMT

Always a good lesson to learn but, in the code you had posted, you didn't have any conditional use of a lag statement. If you did in the actual code you ran then, yes, you could easily run into trouble.

However, its not because the statement can't be used reliably but, more so, that they will be doing things other than what you had intended. But quite reliably!

Re: Help with LAG or RETAIN?

iiibbb — Tue, 07 Feb 2012 14:32:42 GMT

I kind-of disagree.

--------------------------------

if AAA ne Lag1(AAA) then BBB=BBB+1; else BBB=BBB;

--------------------------------

LagAAA = Lag1(AAA);

if AAA ne LagAAA then BBB=BBB+1; else BBB=BBB;

Logically, these should be the same. But based on this experience, they do not yield the same result in certain conditional statements.

But thank you for solving my problem. And now I know how to do it with the lag statement too.

Re: Help with LAG or RETAIN?

art297 — Tue, 07 Feb 2012 14:41:15 GMT

Would you post some data and code that shows a situation where you get different results using the two methods?

The following example appears to produce the exact same result:

data test;

input aaa bbb;

cards;

1 1

1 2

2 3

2 4

1 5

3 6

3 7

3 8

4 9

;

data test1;

set test;

if AAA ne Lag1(AAA) then BBB=BBB+1;

else BBB=BBB;

run;

data test2;

set test;

LagAAA = Lag1(AAA);

if AAA ne LagAAA then BBB=BBB+1;

else BBB=BBB;

run;

Re: Help with LAG or RETAIN?

data_null__ — Tue, 07 Feb 2012 14:52:40 GMT

Art,

Notice the words that iiibbb used "result in certain conditional statements."

I suspect you are not being told the whole story. Most of us know what happens when LAG is executed conditionally.

Re: Help with LAG or RETAIN?

art297 — Tue, 07 Feb 2012 15:01:35 GMT

DN, That is why I posted an earlier response to iiibbb, namely: Always a good lesson to learn but, in the code you had posted, you didn't have any conditional use of a lag statement. If you did in the actual code you ran then, yes, you could easily run into trouble.

Yes, I agree that using lag conditionally requires that one knows how the cue works, but I'm afraid that iiibbb will throw the baby out with the bath water for not understanding what is meant by conditional use.

Re: Help with LAG or RETAIN?

iiibbb — Tue, 07 Feb 2012 16:07:11 GMT

I'll admit I'm a neophite. I wish I had the code that botched my sorting. I've already replaced it with code that works (I generally like to destroy non-working code as quick as possible)

However, I'm certain I had a well laid out logic that didn't work when it was built into the if/then statement... and worked as soon as I created a stand-alone variable.

Needless to say, a learning experience, and I hope that I will come to better understand how this works in the future. I've not worked with data like this before and I'm learning all sorts of new and fun things.

At least I can tell when something is not working

Re: Help with LAG or RETAIN?

iiibbb — Tue, 07 Feb 2012 16:09:32 GMT

Can anyone suggest a good tutorial for more advanced data step functions?

This sort of thing (http://www2.sas.com/proceedings/sugi30/134-30.pdf) I know inside and out.

Re: Help with LAG or RETAIN?

art297 — Tue, 07 Feb 2012 16:20:25 GMT

A fairly simple article that describes the problem with using the lag function, conditionally, is shown at: http://www.nesug.org/proceedings/nesug06/cc/cc32.pdf

There is a site that has all of the global forum papers as well as many of the regional papers: http://www.lexjansen.com/

There is always the documentation: http://support.sas.com/documentation/93/index.html

and sasCommunity.org articles: http://www.sascommunity.org/wiki/Main_Page

and many, many books by SAS users: http://support.sas.com/community/authors/index.html

Re: Help with LAG or RETAIN?

Astounding — Tue, 07 Feb 2012 16:32:23 GMT

Two authors you can look for: Ron Cody and Ben Cochran. You may have to search through the titles, though, to find the papers you're looking for.

Good luck.

Re: Help with LAG or RETAIN?

art297 — Tue, 07 Feb 2012 16:59:09 GMT

Astounding: Absolutely nothing wrong with reading those authors, but I definitely wouldn't limit who I'd suggest reading. There are many, many outstanding authors and not all of them have elected to write books. I'd list some of my own favorites, but that would take a book in itself.