List of ID with multiple conditions either or

[mahossain]

I want to create a sub-data set with list of all variables with the following conditions:
if IDs (2102-202) are with TRCD =LDIAM or SAXIS then select all  IDs (2102-202) with SAXIS only 

and if IDs (2102-205) are with TRCD =LDIAM only then select all IDs  (2102-205) where TRCD=LDIAM

and if IDs (2102-203) are with TRCD=SAXIS then then select all IDs (2102-203) where TRCD=SAXIS  

From the following data;

ID                 TRLNKID      TRCD      ScanDate
2102-202       T01             LDIAM      11/1/2018
2102-208       T02             SAXIS        7/3/2018
2102-202      T02             SAXIS        5/4/2018
2102-202      T04             LDIAM       5/4/2018
2102-203      T01             SAXIS        5/25/2018
2102-203      T01             SAXIS        8/14/2018
2102-203      T01             SAXIS        5/5/2020
2102-203      T04             SAXIS        5/25/2018
2102-205      T01            LDIAM        6/14/2018
2102-205      T01            LDIAM        3/6/2020
2102-206      T02            SAXIS        6/28/2018
2102-206      T03           LDIAM         6/28/2018
2102-207      T02           SAXIS         6/25/2018
2102-207      T03           SAXIS         1/15/2019
2102-208      T01          LDIAM         7/3/2018
2102-208      T02          SAXIS          7/3/2018

It would be great help if anyone of you can help me. I'm new in this forum pls let me know if you need additional information. 

[mklangley]

I'm not sure I understand your objective. Are you just wanting the rows where one of those three conditions is satisfied? Or just the list of distinct IDs that satisfy one of those conditions?

This code will select the rows that satisfy one of those three conditions (as I understand them):

data have;
    input ID $ TRLNKID $ TRCD $ ScanDate :mmddyy10.;
    format ScanDate mmddyy10.;
    datalines;
    2102-202 T01 LDIAM 11/1/2018
    2102-208 T02 SAXIS 7/3/2018
    2102-202 T02 SAXIS 5/4/2018
    2102-202 T04 LDIAM 5/4/2018
    2102-203 T01 SAXIS 5/25/2018
    2102-203 T01 SAXIS 8/14/2018
    2102-203 T01 SAXIS 5/5/2020
    2102-203 T04 SAXIS 5/25/2018
    2102-205 T01 LDIAM 6/14/2018
    2102-205 T01 LDIAM 3/6/2020
    2102-206 T02 SAXIS 6/28/2018
    2102-206 T03 LDIAM 6/28/2018
    2102-207 T02 SAXIS 6/25/2018
    2102-207 T03 SAXIS 1/15/2019
    2102-208 T01 LDIAM 7/3/2018
    2102-208 T02 SAXIS 7/3/2018
    ;
run;

data want;
    set have;
    where (ID = '2102-202' and TRCD = 'SAXIS')
       or (ID = '2102-205' and TRCD = 'LDIAM')
       or (ID = '2102-203' and TRCD = 'SAXIS');
run;

[mahossain]

Thank you!

Yes, I want list of distinct IDs that satisfy the conditions. One ID my have in multiple rows which will fulfill the conditions. I gave here a small portion of data my whole data set. 

[mahossain]

Thank you!

Yes, I want list of distinct IDs that satisfy the conditions. One ID may have in multiple rows with either LDIAM or SAXIS.

I gave here a small portion of data my whole data set. 

[mklangley]

Can you post what you expect the output to look like for this sample data?

[mahossain]

Sure. 

This is my dataset;

ID TRLNKID TRCD ScanDate
2102-202 T01 LDIAM 11/1/2018
2102-202 T02 SAXIS 5/4/2018
2102-202 T04 LDIAM 5/4/2018
2102-203 T01 SAXIS 5/25/2018
2102-203 T01 SAXIS 8/14/2018
2102-203 T01 SAXIS 5/5/2020
2102-203 T04 SAXIS 5/25/2018
2102-205 T01 LDIAM 6/14/2018
2102-205 T01 LDIAM 3/6/2020
2102-206 T02 SAXIS 6/28/2018
2102-206 T03 LDIAM 6/28/2018
2102-207 T02 SAXIS 6/25/2018
2102-207 T03 SAXIS 1/15/2019
2102-208 T01 LDIAM 7/3/2018
2102-208 T02 SAXIS 7/3/2018

This is what i'm expecting: 

ID TRLNKID TRCD ScanDate
2102-202 T02 SAXIS 5/4/2018
2102-203 T01 SAXIS 5/25/2018
2102-203 T01 SAXIS 8/14/2018
2102-203 T01 SAXIS 5/5/2020
2102-203 T04 SAXIS 5/25/2018
2102-205 T01 LDIAM 6/14/2018
2102-205 T01 LDIAM 3/6/2020
2102-206 T02 SAXIS 6/28/2018
2102-207 T02 SAXIS 6/25/2018
2102-207 T03 SAXIS 1/15/2019
2102-208 T02 SAXIS 7/3/2018

Note: If an ID have values 'SAXIS' and 'LDIAM' i will select all those IDs with 'SAXIS'; if ID have is only 'LDIAM' then i i'll select all those IDs with 'LDIAM' and if the ID have values only 'SAXIS' i'll select all those IDs with 'SAXIS'.  

[mklangley · Accepted solution]

This should work, for the sample data you provided (i.e. having only those two TRCDs).

As I understood it, this is your logic for each distinct ID:

   1. If any of the records for that ID have TRCD = 'SAXIS', then output only those records where TRCD = 'SAXIS'

   2. Otherwise, if any of the records for that ID have TRCD = 'LDIAM', then output only those records where TRCD = 'LDIAM'

Does that seem correct?

proc sort data=have out=have_sorted; by ID descending TRCD; run;

data want;
    set have_sorted;
    by ID;
    retain only_saxis;
    if first.ID then do;
        if TRCD = 'SAXIS' then only_saxis = 1;
        else only_saxis = 0;
    end;
    if only_saxis = 1 and TRCD = 'SAXIS' then output;
    else if only_saxis = 0 and TRCD = 'LDIAM' then output;
run;