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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: