AND/OR Logic

Accepted Solution Solved
Reply
Super Contributor
Posts: 543
Accepted Solution

AND/OR Logic

Hi.

I have the following scenario

data have;

    input lab_name $15.;

    cards;

hemoglobin

hemoglobin A1C

;

I want to keep the record with "hemoglobin" not the one with A1C.

This is what I tried to do - the original data set has millions of records (like over 100)

proc sql;

    create table want as

    select lab_name

    from have

    where (lab_name like "HEMOG%" or lab_name %nrstr(like "%GLOBIN"))

        and (lab_name %nrstr(not like "%A1C"));

quit;

I don't know how to set my logic.

Smiley Sad

Any help would be appreciated it.

Regards,

Anca,


Accepted Solutions
Solution
‎09-25-2013 11:07 PM
Super User
Super User
Posts: 6,498

Re: AND/OR Logic

Your logic issue is in combining conditions like this:

X NOT LIKE 'Y'  OR X NOT LIKE 'Z'

For any value of X that condition is true.

If X='Y' then it is NOT like 'Z'

else if X='Z' then it is NOT like 'Y'

and if X is any other value is not like either of them.

View solution in original post


All Replies
Super User
Posts: 5,255

Re: AND/OR Logic

What variations in values do you expect in the data set?

Maybe you could just use scan()-function in some way:

where scan(lab_name,1) = 'hemoglobin' and scan(lab_name,2) is null

Data never sleeps
Super Contributor
Posts: 543

Re: AND/OR Logic

Hi ,

there are about (exactly) 3124 variations of how hemoglobin is spelled - some of which include the A1C either in the front of the (variation) of the hemoglobin word, or at the end.

I want to keep the Hemoglobin, but not the hemoglobin A1C.

Smiley Happy

Smiley Sad

Respected Advisor
Posts: 3,777

Re: AND/OR Logic

upcase(lab_name)

Super Contributor
Posts: 543

Re: AND/OR Logic

I apologize data _null_

it was my mistake when I entered the example here

the upper lower case is taken care of.

It is the logic that I struggle with.

Super User
Posts: 17,771

Re: AND/OR Logic

Parse out the A1C if it exists using tranwrd and then use a fuzzy/soundex match perhaps?

untested and probably wrong code Smiley Happy

tranwrd(lab_name, "A1C", " ") =* "HEMOGLOBIN"

I think the =* is soundex in SQL...but my brains fuzzy at the moment.

Super Contributor
Posts: 543

Re: AND/OR Logic

Thank you, .

I will - After I understand a bit.

Attached is the snippet of my code, as I try to restrict the data to at least some subset that looks like hemoglobin.

Attachment
Super User
Posts: 17,771

Re: AND/OR Logic

rather than the code could you post the proc freq result from lab_name, the 3124 variations or at least the ones you can't filter out.

Solution
‎09-25-2013 11:07 PM
Super User
Super User
Posts: 6,498

Re: AND/OR Logic

Your logic issue is in combining conditions like this:

X NOT LIKE 'Y'  OR X NOT LIKE 'Z'

For any value of X that condition is true.

If X='Y' then it is NOT like 'Z'

else if X='Z' then it is NOT like 'Y'

and if X is any other value is not like either of them.

Super Contributor
Posts: 543

Re: AND/OR Logic

Thank you.

I think a bit clearer today.

Smiley Happy

Respected Advisor
Posts: 3,777

Re: AND/OR Logic

Anca tilea wrote:

I apologize data _null_

it was my mistake when I entered the example here

the upper lower case is taken care of.

It is the logic that I struggle with.

As do I.  I forgot I add the essential parenthesis.

data have;
    input lab_name $15.;
   
cards;
hemoglobin
hemoglobin A1C
;
options merror=0;
proc sql;
   
select lab_name
   
from have
   
where (upcase(lab_name) like "HEMOG%" or upcase(lab_name) like "%GLOBIN")
       
and lab_name not like %nrstr("%A1C");
  
quit;

options merror=1;
Super Contributor
Posts: 543

Re: AND/OR Logic

Dear All,

I believe the problem was in my assumption that  lab_name LIKE "%A1C" will take care of the cases "Hemoglobin A1C", however, it only took care of the "HemoglobinA1C" cases.

So, is there some known fact that "%PATTERN" doesn't account for spaces?

THANK YOU!!!!

Anca.

Occasional Contributor
Posts: 14

Re: AND/OR Logic

Here's a simplified, non SQL solution:

data have;

    input lab_name $15.;

    cards;

hemoglobin

hemoglobin A1C

A1Chemoglobin

hemoA1Cglobin

hemo

globin

;

data temp;

  set have;

  where lab_name ^? 'A1C';

run;

proc print data=temp noobs;run;

Enjoy your day Smiley Happy

Super Contributor
Posts: 543

Re: AND/OR Logic

Hi ,

The "^?" translates to " not don't know for sure but know for sure not A1C" ?

Since I have more than 100mil records, and we use Transact SQL for this...a data step would not be feasible.

Thank you!

Anca.

Occasional Contributor
Posts: 14

Re: AND/OR Logic

Ok...SQL version.  If the intent is to NOT select lab_name if it contains "A1C" then simply use a NOT CONTAINS statement similar to the "^?" version in data steps:

data have;

    input lab_name $15.;

    cards;

hemoglobin

hemoglobin A1C

A1Chemoglobin

hemoA1Cglobin

hemo

globin

;

proc sql;

  create table want as

  select lab_name

  from have

  where lab_name not contains 'A1C';

quit;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 558 views
  • 6 likes
  • 6 in conversation