the following data structure is given:
| ID | CASE | VALUE | DUMMY |
|---|---|---|---|
| 123 | 4736 | E10 | 1 |
| 123 | 1254 | N65 | 0 |
| 123 | 0997 | E11 | 1 |
| 123 | 7655 | x | 0 |
| 987 | 1234 | x | 0 |
| 987 | 6376 | E10 | 1 |
| 987 | 0980 | E18 | 0 |
I want to know: How can i create a new variable that includes if someone actually has the combination of E10 AND E11 (0,1) as follows:
| ID | CASE | VALUE | DUMMY | Has the combination |
|---|---|---|---|---|
| 123 | 4736 | E10 | 1 | 1 |
| 123 | 1254 | N65 | 0 | 1 |
| 123 | 0997 | E11 | 1 | 1 |
| 123 | 7655 | x | 0 | 1 |
| 987 | 1234 | x | 0 | 0 |
| 987 | 6376 | E10 | 1 | 0 |
| 987 | 0980 | E18 | 0 | 0 |
Thanks in advance,
PP
>Solution :
Assumption is that the data is already sorted by id.
data want;
do until (last.id);
set have;
by id;
if value = 'E10' then E10 = 1;
if value = 'E11' then E11 = 1;
end;
do until (last.id);
set have;
by id;
if E10 = 1 and E11 = 1 then combination = 1;
else combination = 0;
output;
end;
drop E:;
run;
id case value dummy combination
123 4736 E10 1 1
123 1254 N65 0 1
123 997 E11 1 1
123 7655 x 0 1
987 1234 x 0 0
987 6376 E10 1 0
987 980 E18 0 0