topic Re: How to sum lagged values if a condition is met? in SAS Programming

How to sum lagged values if a condition is met?

mamun85 — Thu, 27 Jun 2019 21:58:18 GMT

Hi,

I want to create a new dataset (or a column in the existing dataset) that adds the lagged values if a condition is met. Say, the condition is when:

lagDays<7

then add the corresponding value for the variable "chrg" with its preceding value. Here is an example for your consideration:

data have; 
input id $3. chrg lagDays;
datalines;
001 19 .
001 30 23
001 23 45
001 35 1
001 29 2
002 66 .
002 59 34
002 70 19
003 55 .
003 90 10
003 91 4
003 19 6
003 33 1
003 45 22
003 87 1
003 90 1
003 43 45
;
run;

data want; 
input id $3. chrg lagDays adjchrg $20.;
datalines;
001 19 .	19
001 30 23 	30
001 23 45 	23
001 35 1 	23+35
001 29 2 	23+35+29
002 66 . 	66
002 59 34	59
002 70 19	19
003 55 .	55
003 90 10	90
003 91 4	90+91
003 19 6	90+91+19
003 33 1	90+91+19+33
003 45 22	45
003 87 1	45+87
003 90 1	45+87+90
003 43 45	45
;
run;

proc print data=have; run;
proc print data=want; run;

Thanks in advance.

Re: How to sum lagged values if a condition is met?

PaigeMiller — Thu, 27 Jun 2019 22:07:22 GMT

I'll take a crack, but I'm at home, I have no access to SAS to test this code

data want; 
set have;
by id;
if first.id or lagdays>=7 then sum=chrg;
if lagdays<7 then sum+chrg;

datalines;
001 19 .
001 30 23
001 23 45
001 35 1
001 29 2
002 66 .
002 59 34
002 70 19
003 55 .
003 90 10
003 91 4
003 19 6
003 33 1
003 45 22
003 87 1
003 90 1
003 43 45
;
run;

Re: How to sum lagged values if a condition is met?

mamun85 — Thu, 27 Jun 2019 22:12:38 GMT

Thank you so much! It works like a champ! Really appreciate it!