I have table in SAS Enterprise Guide like below:
ID | val1| val2
----|-----|-----
123 | M | M
123 | M | P
123 | P | P
444 | PR | PR
444 | PR | PR
567 | PR | M
567 | M | M
99 | PR | P
And I need to creeate new column "col1" with values 0/1:
- If some ID never changed the value PR from column "val1" to the value of M or P in column "val2" then this ID has 1 else 0
So, as a result I need something like below:
ID | val1| val2| col1
----|-----|-----|----
123 | M | M | 1
123 | M | P | 1
123 | P | P | 1
444 | PR | PR | 1
444 | PR | PR | 1
567 | PR | M | 0
567 | M | M | 0
99 | PR | P | 0
Because:
- 123 - has 1 in "col1" because has never changed PR to M or P
- 444 - has 1 in "col1" because has never changed PR to M or P
- 567 - has 0 because changed PR to M
- 99 - has 0 because changed PR to P
How can I do that in PROC SQL in SAS Enterprise Guide ?