topic Re: List of ID with multiple conditions either or in SAS Programming

List of ID with multiple conditions either or

mahossain — Mon, 06 Jul 2020 15:03:31 GMT

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.

Re: List of ID with multiple conditions either or

mklangley — Mon, 06 Jul 2020 16:11:54 GMT

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;

Re: List of ID with multiple conditions either or

mahossain — Mon, 06 Jul 2020 16:27:19 GMT

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.

Re: List of ID with multiple conditions either or

mahossain — Mon, 06 Jul 2020 16:30:40 GMT

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.

Re: List of ID with multiple conditions either or

mklangley — Mon, 06 Jul 2020 16:34:25 GMT

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

Re: List of ID with multiple conditions either or

mahossain — Mon, 06 Jul 2020 16:53:50 GMT

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'.

Re: List of ID with multiple conditions either or

mklangley — Mon, 06 Jul 2020 19:46:45 GMT

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;