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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: