using Lag in loop

[buckeyefisher]

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;

[ballardw]

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: ;

[Ksharp]

Post your sample data the output you want.

[buckeyefisher]

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

[Ksharp · Accepted solution]

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

[buckeyefisher]

Xia,

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

Thanks a lot.