Where statement in SAS Code and Usage

[cbrotz]

I used to use the IN:() function in SAS.  It does not seem to work anymore or I am writing wrong.  I see that a WHERE statement can be used with a wildcard?  the second line of code below seems to work but the AND operator is not turning blue.  Not a big deal but kind of strange.  Can anyone fill me in if I am missing something here?

I want ALL level2 that contain the word  "PLACARD"  Thank you.

IF ORGGROUP = 'KEY' AND LEVEL2 IN: ('PLACARDS');
where ORGGROUP='KEY' AND LEVEL2 like '%PLACARDS%';

[ballardw · Accepted solution]

Show you log with the IN attempt and why you think it doesn't work, as in values of the variable.

I suggest the most likely case is that your variable Level2 does not have the CASE (upper case as shown) that you supplied. See this example

data example;
   x = 'abc';
   y = x in: ('AB');
   z = x in: ('ab');
run;

OR that you are looking for something other than the beginning of the string such as this:

data example;
   x = 'abc';
   z = x in: ('bc');
run;

IN: is going to expect the compared value to start the string and match case.

[cbrotz]

Oh that is it.  IN only works when it is the start of the variable which in this case it is not.  I just don't remember "where" statements working in a SAS data step. The second line works but looks weird to me. Thanks for your help!

[ballardw]

---

@cbrotz wrote:

Oh that is it.  IN only works when it is the start of the variable which in this case it is not.  I just don't remember "where" statements working in a SAS data step. The second line works but looks weird to me. Thanks for your help!

---

Not quite.  IN: , with the colon, is "begins with". A simple IN (<list of values>) does an exact match.

[Tom]

Your WHERE condition is checking for the PLACARDS string anywhere in the string LEVEL2.

But your IN operator is checking only for PLACARDS at the start of the string.

If you want to check for PLACARDS anywhere in the string using the INDEX() function instead of the IN operator.