DATA Step, Macro, Functions and more

Lag function with if condition

Reply
Occasional Contributor
Posts: 13

Lag function with if condition

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;

 

Super User
Posts: 5,085

Re: Lag function with if condition

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;

Super User
Posts: 6,948

Re: Lag function with if condition

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 2 replies
  • 170 views
  • 0 likes
  • 3 in conversation