BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
buckeyefisher
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

5 REPLIES 5
ballardw
Super User

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
Super User

Post your sample data the output you want.

buckeyefisher
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

Xia,

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

Thanks a lot.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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