Hi,
I have a data set that looks like this:
Group | Inc |
A | 0 |
A | 9 |
A | 1 |
A | 8 |
A | 2 |
A | 7 |
A | 3 |
A | 6 |
B | 90 |
B | 0 |
B | 80 |
B | 10 |
B | 70 |
B | 20 |
B | 60 |
B | 30 |
. | . |
. | . |
. | . |
I want to add columns with one- and four-period lags of "Inc" by group. The answer should look like this:
Group | Inc | LQInc | LYInc |
A | 0 | . | . |
A | 9 | 0 | . |
A | 1 | 9 | . |
A | 8 | 1 | . |
A | 2 | 8 | 0 |
A | 7 | 2 | 9 |
A | 3 | 7 | 1 |
A | 6 | 3 | 8 |
B | 90 | . | . |
B | 0 | 90 | . |
B | 80 | 0 | . |
B | 10 | 80 | . |
B | 70 | 10 | 90 |
B | 20 | 70 | 0 |
B | 60 | 20 | 80 |
B | 30 | 60 | 10 |
. | . | . | . |
. | . | . | . |
. | . | . | . |
This is my attempt at coding this problem:
DATA want;
SET have;
by group;
retain n lqinc_temp lyinc_temp;
lqinc = lqinc_temp;
lyinc = lyinc_temp;
if first.group then
n = 1 and
lqinc = .;
if n < 4 then lyinc = .;
n = sum(n,1);
lqinc_temp = inc;
lyinc_temp = lag4(inc);
run;
The answer I get looks like this:
Group | Inc | LQInc | LYInc |
A | 0 | 0 | . |
A | 9 | 9 | . |
A | 1 | 1 |
|
A | 8 | 8 |
|
A | 2 | 2 | 0 |
A | 7 | 7 | 9 |
A | 3 | 3 | 1 |
A | 6 | 6 | 8 |
B | 90 | 90 | . |
B | 0 | 0 | . |
B | 80 | 80 | 3 |
B | 10 | 10 | 6 |
B | 70 | 70 | 90 |
B | 20 | 20 | 0 |
B | 60 | 60 | 80 |
B | 30 | 30 | 10 |
. | . | . | . |
. | . | . | . |
. | . | . | . |
I can't quite understand what SAS is doing, but it appears to be cycling through each record more than once. Any suggestions for fixing my code would be much appreciated.
Thanks
This is invalid:
if first.group then
n = 1 and
lqinc = .;
You likely have some order of operations things going on as well, but I think you want:
if first.group then do;
n=1;
lqinc = .;
end;
This could be simplified though to:
data want; set have; by group; *create counter; if first.group then count=0; count+1; *get lagged values; lqinc = lag(inc); lyinc = lag4(inc); *if not enough records reset lags to missing; if count <2 then call missing(lginc); if count<4 then call missing(lyinc); run;
This is invalid:
if first.group then
n = 1 and
lqinc = .;
You likely have some order of operations things going on as well, but I think you want:
if first.group then do;
n=1;
lqinc = .;
end;
This could be simplified though to:
data want; set have; by group; *create counter; if first.group then count=0; count+1; *get lagged values; lqinc = lag(inc); lyinc = lag4(inc); *if not enough records reset lags to missing; if count <2 then call missing(lginc); if count<4 then call missing(lyinc); run;
Thanks Reeza. That worked great.
An alternative way :
data want; set have; *get lagged values; lqinc = lag(inc); lyinc = lag4(inc); *if not enough records reset lags to missing; if group ne lag(group) then call missing(lginc); if group ne lag4(group) then call missing(lyinc); run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.