Solved
Contributor
Posts: 63

using Lag in loop

Hi,

I want to sum the lagged values for a given window of years.

For example if the Year = 1990 then window is between 1983 to 1988. I want to add values of a field in this window.  I wrote the following code. The system is not giving any errors but not calculating values. There can be some missing years so I can not put an exact counter of 7

/*** first step to calculate window

data A;

set B;

wind2 = Year-2;

wind1 = Year-7;

/****lag in loop

%macro test;

data C;

set B;

%do n=1 %to 20;

if wind1 le lag&n.(Year) and wind2 ge lag&n.(Year) then CoDevelopment_cnt = sum(CoDevelopment_cnt,lag&n.(CoDevelopment));

%end;

run;

%mend;

%test;

Accepted Solutions
Solution
‎03-21-2015 11:21 AM
Super User
Posts: 10,787

Re: using Lag in loop

OK.

```data have;
input Company    \$  Year     Development  ;
cards;
A                    1990     1
A                    1992     1
A                    1993     0
A                    1994     1
A                    1995     0
A                    1996     1
A                    1998     0
B                    1991     1
;
run;
proc sql;
create table want as
select *,(select coalesce(sum(Development),0) from have where Company=a.Company and Year between a.Year-7 and a.Year-2 ) as count
from have as a;
quit;

```

Xia Keshan

All Replies
Super User
Posts: 13,583

Re: using Lag in loop

IF with LAG is an invitation to frustration. The simplest approach is to ALWAYS calculate the lag values before any if or other conditional type statements.

%do n = 1 %to 20;

Lyear&n = lag&n (year);

LCoDevelopment&n = lag&n(CoDevelopment);

%end;

use the variables created in the IF.

And since you are likely not to want those variables hanging around:

Drop LYear: LCoDevelopment: ;

Super User
Posts: 10,787

Re: using Lag in loop

Post your sample data the output you want.

Contributor
Posts: 63

Re: using Lag in loop

Hi Xia,

I want to count Development in 5 years for each Year. For example for Year = 1998 the window is from 1996 to 1991  (Year - 2 to Year - 7)

input :

Company      Year     Development

A                    1990     1

A                    1992     1

A                    1993     0

A                    1994     1

A                    1995     0

A                    1996     1

A                    1998     0

B                    1991     1

B

.

.

output needed

Company      Year     Development   Window                Count

A                    1990     1                       1988-1983            0

A                    1992     1                       1990-1985            1

A                    1993     0                       1991-1986            1

A                    1994     1                       1992-1987            2

A                    1995     0                       1993-1988            2

A                    1996     1                       1994-1989            3

A                    1998     0                       1996-1991            3

B                    1991     1                       1989-1984            0

B

.

.

Thanks

Solution
‎03-21-2015 11:21 AM
Super User
Posts: 10,787

Re: using Lag in loop

OK.

```data have;
input Company    \$  Year     Development  ;
cards;
A                    1990     1
A                    1992     1
A                    1993     0
A                    1994     1
A                    1995     0
A                    1996     1
A                    1998     0
B                    1991     1
;
run;
proc sql;
create table want as
select *,(select coalesce(sum(Development),0) from have where Company=a.Company and Year between a.Year-7 and a.Year-2 ) as count
from have as a;
quit;

```

Xia Keshan

Contributor
Posts: 63

Re: using Lag in loop

Xia,

This works seamlessly. Prc SQL is much cleaner than Lag in loop.

Thanks a lot.

🔒 This topic is solved and locked.