topic Re: using Lag in loop in SAS Programming

using Lag in loop

buckeyefisher — Fri, 20 Mar 2015 20:53:01 GMT

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;

Re: using Lag in loop

ballardw — Fri, 20 Mar 2015 21:42:56 GMT

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

Re: using Lag in loop

Ksharp — Sat, 21 Mar 2015 07:25:56 GMT

Post your sample data the output you want.

Re: using Lag in loop

buckeyefisher — Sat, 21 Mar 2015 13:23:51 GMT

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

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

Thanks

Re: using Lag in loop

Ksharp — Sat, 21 Mar 2015 15:21:54 GMT

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

Re: using Lag in loop

buckeyefisher — Sat, 21 Mar 2015 15:45:33 GMT

Xia,

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

Thanks a lot.