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: 10,023

Re: using Lag in loop

Posted in reply to buckeyefisher

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: 11,343

Re: using Lag in loop

Posted in reply to buckeyefisher

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,023

Re: using Lag in loop

Posted in reply to buckeyefisher

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,023

Re: using Lag in loop

Posted in reply to buckeyefisher

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.

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

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