topic Re: Multi-period lags in a by group in SAS Programming

Multi-period lags in a by group

RL997 — Thu, 30 May 2019 16:32:46 GMT

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

Re: Multi-period lags in a by group

Reeza — Thu, 30 May 2019 16:37:01 GMT

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;

Re: Multi-period lags in a by group

RL997 — Thu, 30 May 2019 18:12:38 GMT

Thanks Reeza. That worked great.

Re: Multi-period lags in a by group

Ksharp — Fri, 31 May 2019 14:13:55 GMT

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;