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;
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
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: ;
Post your sample data the output you want.
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
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
Xia,
This works seamlessly. Prc SQL is much cleaner than Lag in loop.
Thanks a lot.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.