Data sample:
ID | Test | VisitType |
1 | 0 | S |
1 | 1 | S |
1 | 1 | U |
1 | 0 | S |
1 | 1 | S |
1 | 1 | S |
1 | 1 | S |
2 | 1 | S |
2 | 1 | S |
2 | 0 | S |
2 | 1 | S |
2 | 1 | U |
2 | 1 | U |
2 | 1 | U |
2 | 1 | U |
2 | 0 | S |
2 | 0 | S |
2 | 0 | S |
Based on the above data, I'm trying to create a variable (VarWant) that, for each ID, is a 1 if VisitType=S and Test=1 OR is a 1 if VisitType=S, Test=0, BUT the preceding VisitType=U and Test=1. For clarification, here is what I'm looking for:
ID | Test | VisitType | VarWant |
1 | 0 | S | 0 |
1 | 1 | S | 1 |
1 | 1 | U | 1 |
1 | 0 | S | 1 |
1 | 1 | S | 1 |
1 | 1 | S | 1 |
1 | 1 | S | 1 |
2 | 1 | S | 1 |
2 | 1 | S | 1 |
2 | 0 | S | 0 |
2 | 1 | S | 1 |
2 | 1 | U | 1 |
2 | 1 | U | 1 |
2 | 1 | U | 1 |
2 | 1 | U | 1 |
2 | 0 | S | 1 |
2 | 0 | S | 0 |
2 | 0 | S | 0 |
I know I can use a retain statement for this, but I can't figure out the logic statement that should accompany it. Thanks in advance for the help!
The LAG function should make this straightforward:
data want;
set have;
by id;
prior_type = lag(VisitType);
prior_test = lag(test);
if first.id then call missing(prior_type, prior_test);
VarWant = (VisitType="S" and test=1) or (VisitType="S" and test=0 and prior_type="U" and prior_test=1);
run;
The LAG function should make this straightforward:
data want;
set have;
by id;
prior_type = lag(VisitType);
prior_test = lag(test);
if first.id then call missing(prior_type, prior_test);
VarWant = (VisitType="S" and test=1) or (VisitType="S" and test=0 and prior_type="U" and prior_test=1);
run;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.