sas recursive lag by id

Question

I am trying to do a recursive lag in sas, the problem that I just learned is that x = lag(x) does not work in SAS.

The data I have is similar in format to this:

id      date          count     x
a       1/1/1999      1         10
a       1/1/2000      2         .
a       1/1/2001      3         .
b       1/1/1997      1         51
b       1/1/1998      2         .

What I want is that given x for the first count, I want each successive x by id to be the lag(x) + some constant.

For example, lets say: if count > 1 then x = lag(x) + 3. The output that I would want is:

id      date          count     x
a       1/1/1999      1         10
a       1/1/2000      2         13
a       1/1/2001      3         16
b       1/1/1997      1         51
b       1/1/1998      2         54

Answer 1

Yes, the lag function in SAS requires some understanding. You should read through the documentation on it (http://support.sas.com/documentation/cdl/en/lefunctionsref/67398/HTML/default/viewer.htm#n0l66p5oqex1f2n1quuopdvtcjqb.htm)

When you have conditional statements with a lag inside the "then", I tend to use a retained variable.

data test;
input id  $ date  count     x;
informat date anydtdte.;
format date date9.;
datalines;
a       1/1/1999      1         10
a       1/1/2000      2         .
a       1/1/2001      3         .
b       1/1/1997      1         51
b       1/1/1998      2         .
;
run;

data test(drop=last);
set test;
by id;
retain last;
if ^first.id then do;
    if count > 1 then
        x = last + 3;
end;

last = x;
run;