Subsetting Data within Groups in SAS

Question

I'm a new SAS user and having some difficulty creating the data subsets I want. I have survey data where patients get prompted to complete a survey multiple times. If they complete the survey, they won't get prompted for another 30 days. If they decline or do nothing, they will get prompted again (can be within 30 days).

This is the data table I have:

Data Have;
Input RowId $ PatientId $ Action $ Date;
Datalines;
1 101 Prompted 201603
2 101 Prompted 201605
3 101   Yes 201605
4 102   Prompted 201606
5 103 Prompted 201612
6 103   No  201612
7 104 Prompted 201603 
8 104 Yes 201603 
9 105   Prompted 201608
10 105 Yes 201608
11 105 Prompted 201702
12 105 No 201702
13 106 Prompted 201703
14 106 Yes 201703
15 106 Prompted 201706
16 106 Yes 201706
run;    

I want to create two subsets of data 1) Respondents -When there are multiple prompts, I want to keep the one with the completed survey -If there are multiple surveys over time, I want to keep the earliest survey

2) Non-respondents -Patients that were prompted but did nothing are considered non-respondents

I want to produce these two subsets:

Respondents:

 Data Yes;
    Input RowId $ PatientId $ Action $ Date;
    Datalines;
    3 101   Yes 201605
    8 104 Yes 201603 
    10 105 Yes 201608
    14 106 Yes 201703
    run;

Non-respondents:

Data No;
Input RowId $ PatientId $ Action $ Date;
Datalines;
4 102   Prompted 201606
6 103   No  201612
run;

I understand the data isn't very intuitive, and I have tried my best to explain it here. Any help is greatly appreciated!

Answer 1

Using a few techniques together, we can get results fairly simply:

Find the "Yes" patients, keeping only first instance

data yes;
  set have(where=(action="Yes"));
  by patientId;
  if first.patientId;
run;

Find the "No" / "Non-response" Patients using have, yes, and last.

data no;
  merge have(in = H)
         yes(in = Y);
  by patientId;
  if H and not Y and last.patientId;
run;

Results