topic Re: data step execution in SAS Programming

data step execution

ender_akpil — Tue, 20 Dec 2011 21:50:56 GMT

Hi everybody

there is two code which are acting differently. can anybody explain why?

Hint: Lag function puls from queue!!

According to me they must procedure the same result!!!

data test;

INFILE datalines DLM=',' DSD;
input a b c ;
datalines;
4272451,17878,17878
4272451,17878,17878
4272451,17887,17887
4272454,17878,17878
4272454,17881,17881
4272454,17893,17893
4272455,17878,17878
4272455,17878,18200
run;

DATA TEST1;

RETAIN F ( 1) ;

laga = lag(a); lagb=lag(b);

SET TEST;
IF A^=laga OR laga =. THEN do; f=1;end; ELSE IF A=laga AND b>lagb THEN do; f=f+1 ; end;
RUN;

proc print data=test1;

run;

DATA TEST2;
RETAIN F ( 1) ;
SET TEST;
IF A^=LAG(A) OR LAG(A)=. THEN do; f=1;end; ELSE IF A=LAG(A) AND b>LAG(B) THEN do; f=f+1 ; end;
RUN;

proc print data=test2;

run;

Re: data step execution

Tom — Tue, 20 Dec 2011 22:03:48 GMT

The problem is the ELSE clause. This can cause the LAG(A) and LAG(B) functions in the second IF statement not to run on every iteration. So instead of comparing the current value to the immediately preceding value you are comparing it to some value more than one observation earlier.

data step execution

ender_akpil — Tue, 20 Dec 2011 22:18:19 GMT

Thanks Tom, you are right..

data step execution

ender_akpil — Tue, 20 Dec 2011 22:24:07 GMT

Actually i have one more question. Why the lag function in the if and else part acting diffferently? they are on the same input line?

data step execution

art297 — Tue, 20 Dec 2011 22:31:47 GMT

An if statement functions from left to right and stops as soon as its condition is met

data step execution

ender_akpil — Tue, 20 Dec 2011 22:33:55 GMT

in the else part of the if satatement, lag fonctions will pull the value from previous, to determine the previous what value it will check? row number_ obs? _n_? which one?

thanks

Re: data step execution

Tom — Tue, 20 Dec 2011 22:37:04 GMT

You also have trouble with the location of your LAG function calls in the first case. You have placed if BEFORE the SET statement.

This pushes an extra set of missing values onto the stack so that you end up getting the value from two observations before the call.

516  data test2;
517    before=lag(a);
518    set test;
519    after =lag(a);
520    put (a before after) (=);
521  run;
a=1 before=. after=.
a=2 before=. after=1
a=3 before=1 after=2
a=4 before=2 after=3
a=5 before=3 after=4
a=6 before=4 after=5
a=7 before=5 after=6

data step execution

art297 — Tue, 20 Dec 2011 22:38:11 GMT

It functions with a que, using the last value set. However, the last value is only put into the cue when the statement is called.

Re: data step execution

MikeZdeb — Tue, 20 Dec 2011 22:40:52 GMT

hi ... you can have a LAG function within a conditional statement that does execute in every iteration of the data step if you use an IFN function instead of your IF-THEN-ELSE statements...

data test3;

retain f 1;

set test;

f = ifn(a ne lag(a) or missing(lag(a)) , 1 , ifn(a eq lag(a) and b gt lag(b) , f+1 , f ));

run;

the above gives the same result as one of your data step, just modified slightly by moving the LAG statements after the SET statement ...

DATA TEST1;

RETAIN F ( 1) ;

SET TEST;

laga = lag(a); lagb=lag(b);

IF A^=laga OR laga =. THEN do; f=1;end; ELSE IF A=laga AND b>lagb THEN do; f=f+1 ; end;

RUN;

also fyi ... you don't need the DO-END stuff or the parentheses in the RETAIN and you can use a SUM statement ...

DATA TEST1;

RETAIN F 1 ;

SET TEST;

laga = lag(a);

lagb=lag(b);

IF A^=laga OR laga =. THEN f=1;

ELSE

IF A=laga AND b>lagb THEN f+1;

RUN;

and ... since in the first pass through the data step, laga is missing, you can skip the RETAIN statement that gives F an intial value of 1 since F will be assigned a 1 in the first pass and the sum statement (F+1) is an "implied RETAIN" for F

ps you can read more about LAG and IF in Howard Schreier's paper ... "Conditional Lags Don't Have to be Treacherous"

http://www.howles.com/saspapers/CC33.pdf

data step execution

ender_akpil — Tue, 20 Dec 2011 22:46:05 GMT

this is very suprising for me!! why there is a extra missing step i couldnot understand.. but thanks i will think about it..

data step execution

ender_akpil — Tue, 20 Dec 2011 22:47:56 GMT

Thanks for the answer Mike, it was helpful..

But i still searching lag function how determine the previous? if it gets from que why if and else part use different que?

data step execution

Tom — Tue, 20 Dec 2011 22:49:37 GMT

Try putting PUT statements before and after the SET statement to see when the values of the variables from the input dataset change.

data step execution

ender_akpil — Tue, 20 Dec 2011 22:49:59 GMT

thanks for the asnwer, but i still dont get it.. if and else part use same que ? or not? if they use same que then the lag function prodce same result for both part of if staatement.. if they dont use the same que then why? where the que for the else part? how can i reach it?

data step execution

MikeZdeb — Tue, 20 Dec 2011 23:02:41 GMT

hi ... it's not that a different part of the queue is used

LAG gives you the value of a variable from the last time the LAG function was executed, NOT the value of the variable in the previous observation (that's what Tom told you)

so ... if you use IF-THEN-ELSE without the statements ...

LAGA = LAG(A);

LAGB = LAG(B);

then LAG(A) and/or LAG(B) may not get executed during each pass through the data step

Re: data step execution

Tom — Tue, 20 Dec 2011 23:08:34 GMT

And each place in the code where you have a LAG function will keep its own queue.

Look at this example.

data x;
  do i=1 to 10 ;
    lag1=lag(i) ;
    if i ne 4 then lag2=lag(i);
    put (i lag1 lag2) (=);
  end;
run;
i=1 lag1=. lag2=.
i=2 lag1=1 lag2=1
i=3 lag1=2 lag2=2
i=4 lag1=3 lag2=2
i=5 lag1=4 lag2=3
i=6 lag1=5 lag2=5
i=7 lag1=6 lag2=6
i=8 lag1=7 lag2=7
i=9 lag1=8 lag2=8
i=10 lag1=9 lag2=9

data step execution

ender_akpil — Tue, 20 Dec 2011 23:14:56 GMT

OK .. i understand the last time execution.. but then i have to ask last time execution of if part is different from last time execution of else part? since according to to Tom else part is not executed in every iteration? but then there must be a log or something else that with help of it lag knows from where it will continue? isnt it?

Lets create a scnenario: there is data and if then-else statement and lag function in each part:

Observation 1: if part true and lag in the if part is executed. lag in the else part is not executed

Observation 2: if part true and lag in the if part is executed. lag in the else part is not executed

Observation 3: if part false and lag in the if part is not executed. lag in the else part is executed

what is the value of lag in the third observation?

data step execution

Tom — Tue, 20 Dec 2011 23:20:18 GMT

Since it is the first time that it executed the result of any LAG() function calls in the ELSE part on the third iteration will be missing values.

data step execution

ender_akpil — Tue, 20 Dec 2011 23:23:36 GMT

Thanks Tom, this is very nice code..

I have a question about lag2 variable, if lag's in the data step follw their own que then lag2 values should be:

lag2=.

lag2=1

lag2=2

lag2=3

lag2=4

lag2=5

lag2=6

lag2=7

lag2=8

lag2=9

but according to ypur result in line 6 in turns to normal flow?

data step execution

Tom — Wed, 21 Dec 2011 00:03:32 GMT

When LAG() is called the top of the stack is returned and the current value is pushed onto the stack.

When I=1 then missing is returned and 1 is pushed.

When I=2 then 1 is returned and 2 is pushed.

When I=3 then 2 is returned and 3 is pushed.

When I=4 it is skipped so LAG2 stays as 2.

When I=5 then 3 is returned and 5 is pushed.

When I=6 then 5 is returned and 6 is pushed.

....

data step execution

ender_akpil — Wed, 21 Dec 2011 07:58:04 GMT

Thanks Tom..

Still i want to ask, should lag function should use same que? i mean this is more meaningfull isnt it?

I come across with this problem while i was searching equivalent of oracle partitioned analytic query ( row_number over (partition by a order by b asc) )

Is there a anoher way to do this?