topic Re: Lag function with if condition in SAS Programming

Lag function with if condition

e044800 — Fri, 09 Dec 2016 10:27:41 GMT

Hi All,

Below is the program i am running on my SAS Eg. In the last data step i am facing a problem.

Why the lag function is not assigning the value to sum_ncbal whenever the condition is true?

data in;
input seg $ period ncbal;
datalines;
A 1 10
A 2 20
A 3 30
A 4 40
A 5 50
A 6 60
A 7 70
A 8 80
A 9 90
A 10 100
A 11 110
A 12 120
B 1 1
B 2 2
B 3 3
B 4 4
B 5 5
B 6 6
B 7 7
B 8 8
B 9 9
B 10 10
B 11 11
B 12 12
;
run;

proc sort data=in ;
by seg descending period;
run;

Data in1;
set in;
by seg;
if first.seg then count=0;
count+1;
if count = 2 then sum_ncbal= lag(ncbal);
run;

Re: Lag function with if condition

Astounding — Fri, 09 Dec 2016 11:16:14 GMT

LAG is trickier than it looks. It seems that you want the value of NCBAL from the previous observation, but that's not what LAG does. It actually retrieves the value from the last time that the LAG function executed. To get the value from the previous observation, make sure that LAG executes every time. For example:

Data in1;
set in;
by seg;

tempvar = lag(ncbal);

drop tempvar;
if first.seg then count=0;
count+1;
if count = 2 then sum_ncbal= tempvar;
run;

Re: Lag function with if condition

Kurt_Bremser — Fri, 09 Dec 2016 11:17:50 GMT

The lag() functions only fill their queue when called. Calling them conditionally usually results in arbitrary return values.

Do this:

data in1;
set in;
by seg;
if first.seg
then count = 1;
else count + 1;
sum_ncbal = lag(ncbal);
if count = 2 then sum_ncbal = .;
run;

if you already have values in sum_ncbal that need to be preserved, do this

data in1;
set in;
by seg;
if first.seg
then count = 1;
else count + 1;
lag_ncbal = lag(ncbal);
if count = 2 then sum_ncbal = lag_ncbal;
drop lag_ncbal;
run;