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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.