topic Re: Where statement in SAS Code and Usage in SAS Programming

Where statement in SAS Code and Usage

cbrotz — Mon, 31 Jan 2022 17:39:13 GMT

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%';

Re: Where statement in SAS Code and Usage

ballardw — Mon, 31 Jan 2022 17:47:23 GMT

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.

Re: Where statement in SAS Code and Usage

Tom — Mon, 31 Jan 2022 18:31:28 GMT

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.

Re: Where statement in SAS Code and Usage

cbrotz — Mon, 31 Jan 2022 19:25:03 GMT

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!

Re: Where statement in SAS Code and Usage

ballardw — Tue, 01 Feb 2022 14:56:03 GMT

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