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
Use retain statement to do rolling calculation by id.
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;
Use retain statement to do rolling calculation by id.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.