DATA Step, Macro, Functions and more

using Lag in loop

Accepted Solution Solved
Reply
Contributor
Posts: 63
Accepted Solution

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: 9,687

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

View solution in original post


All Replies
Super User
Posts: 10,538

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: 9,687

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: 9,687

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 271 views
  • 3 likes
  • 3 in conversation