BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mick_bill
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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;

View solution in original post

1 REPLY 1
Astounding
PROC Star

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 505 views
  • 2 likes
  • 2 in conversation