Multiple data outcomes retained - Same ID

[Mick_bill]

Hello,

I am trying to produce an outcome which would keep multiple outcomes from the same ID.

The example shows the original dataset. This has shown 3 IDs each with a time stamp on and the outcome from that time stamp. What i am trying to do is to keep when the outcome is restricted but not for just the first occasion, this could be restricted either on the first occastion or this could be restricted after the prior decision was classified as access.

data original;

input KEY $ Outcome $ Time $ ;

id=_n_;

datalines;

1 ACCESS 2

1 RESTRICTED 4

1 ACCESS 6

1 RESTRICTED 8

1 RESTRICTED 10

2 ACCESS 2

2 RESTRICTED 4

2 RESTRICTED 6

2 RESTRICTED 8

2 RESTRICTED 10

3 RESTRICTED 2

3 ACCESS 4

3 RESTRICTED 6

;

run;

The outcome below shows the final outcome i would wish to produce. This would pull out only the relevant data when the data has been classified as restricted and is either the first occasion or has superseded where the same ID had show access prior to this. Initially I have tried a retain but due to the sort and nature this didnt seem to work. I have also thought about a lag function to view the outcome prior and I couldnt get this to work, I tried via matching the outcome previously to the outcome now. This would also not work for when outcome is restricted on the first example.

data output_1;

input KEY $ Outcome $ Time $ ;

id=_n_;

datalines;

1 RESTRICTED 4

1 RESTRICTED 8

2 RESTRICTED 4

3 RESTRICTED 2

3 RESTRICTED 6

;

run;

If possible would it possible to output the remaining information in another datasets. Such as the one below:

data output_2;

input KEY $ Outcome $ Time $ ;

id=_n_;

datalines;

1 ACCESS 2

1 ACCESS 6

1 RESTRICTED 10

2 ACCESS 2

2 RESTRICTED 6

2 RESTRICTED 8

2 RESTRICTED 10

3 ACCESS 4

;

run;

Thank you,

Michael

[Astounding · Accepted solution]

Given that you have created your original data set, the programming should be short:

data first_restricted all_others;
set original;
by key outcome notsorted;
if first.outcome and outcome = "RESTRICTED" 
then output first_restricted;
else output all_others;
run;