Need cumulative totals based on condition

[bharath86]

@Kurt_Bremser

Hi,

Can you please help me with this output. 

Data I have:

ID	Tr_ID	Category	Low	High
111	1	Age	3	4
111	2	Duration	3	3
111	3	Duration	2	2
222	1	Age	60	60
222	2	Duration	24	24

What I get:

ID	Tr_ID	Category	Low	High	Age_Low	Age_High
111	1	Age	3	4	3	4
111	2	Duration	3	3	6	7
111	3	Duration	2	2	5	5
222	1	Age	60	60	60	60
222	2	Duration	24	24	84	84

What I need:

ID	Tr_ID	Category	Low	High	Age_Low	Age_High
111	1	Age	3	4	3	4
111	2	Duration	3	3	6	7
111	3	Duration	2	2	8	9
222	1	Age	60	60	60	60
222	2	Duration	24	24	84	84

Code I use:

data need;

set have;

if Category= 'Duration' then Age_Low=0;

         Age_Low=lag(low)+low;

if Category= 'Duration' then Age_High=0;

         Age_High=lag(high)+High;

if Category ='Age' then    Age_Low= low;

if Category ='Age' then    Age_High= high;

run;

How many ever rows I might have with category as 'Duration' I should be able to take the previous row value and sum this with current row. 

But my code stops executing this logic after first occurrence. Please advise.

Thank you

[hhinohar · Accepted solution]

Use retain statement to do rolling calculation by id.

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=lestmtsref&docsetTarget=p...

Below example uses ifn function but if statement works as well.

data have;
infile datalines dlm="09"x;
input ID Tr_ID Category $ Low High;
datalines;
111	1	Age	3	4
111	2	Duration	3	3
111	3	Duration	2	2
222	1	Age	60	60
222	2	Duration	24	24
;
run;

data want;
	set have;
	by id;
	*first keep values retained;
	retain age_low age_high;
	*if first.id then input low and high values else do rolling calc;
	age_low=ifn(first.id,low,age_low+low);
	age_high=ifn(first.id,high,age_high+high);
run;

[PeterClemmensen]

data have;
input ID Tr_ID Category $ Low High;
datalines;
111 1 Age      3 4
111 2 Duration 3 3
111 3 Duration 2 2
222 1 Age      60 60
222 2 Duration 24 24
;

data want;
   set have;
   by id;
   if first.ID then call missing(age_low, age_high);
   age_low + Low;
   age_high + High;
run;