Check if values in 'A' column contains (1,2,3) but does not contain (4,5,6) within the same table.

[Divya12]

case when

A.col IN ('1','2','3','4')
AND
B.col NOT IN ('5','6','7')

then 

'reason'

end

from

table a

inner join

table b

on ....

group by..;

I get incorrect results. 

Is there any other method to check if a range of values 'in'  column A and 'not in' range of values within the same table and member.

[ballardw]

Your question is not very clear. The subject says "within the same table" but you are obviously using two different tables. Your subject also says A contains (1,2,3) but your code shows 4 as well.

You may need to provide example data from both tables to determine actual logic problem.

As a minimum you really need to show the entire proc step. As shown you appear to be missing an "as variablename" at the end of the CASE statement.

case when
   A.col IN ('1','2','3','4')
   AND
   B.col NOT IN ('5','6','7')
   then 'reason'
end AS RESULTVARIABLE

Better is to copy from the log the entire procedure code and any notes, warnings or errors. Paste that copied text into a code box opened on the forum with the {I} or "running man" icon to preserve text formatting as the message windows

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

[Tom]

You need to show example data that describe what you have and what output you want for that sample data.

Your query is checking if the ONE pair of values of A.COL and B.COL are in those lists.

It sounds like you instead want to test if ANY value of A.COL and ANY value of B.COL are in those lists over ALL of the observations in a group.  To do that you need to use an aggregate function, like MAX() or SUM().

So something like this:

create table want as 
select a.id
     , case when max(A.col IN ('1','2','3','4')) AND max(B.col NOT IN ('5','6','7'))
            then 'reason' else 'NOT reason' 
       end as NEW_VAR
from have1 A
inner join have2 B
on a.id = b.id 
group by a.id
;

Although perhaps you logic is slightly different than that?  That is why examples help to clarify what you want. 

[Divya12]

Okay Let me put it up in a more clear way.

I need to check if any of the conditions is true in the table.

Column1= 13*

AND column2 IN (1, 2, 3)

AND  column2 not IN (4, 5, 6)

AND  ChargedAmount  > 20
OR

Column1= 13* AND
column2 IN (1, 2, 3)

AND column2 IN (4, 5, 6)

AND ChargedAmount  > 100

Can you help in coding the above specification?              

[ballardw]

---

@Divya12 wrote:

Okay Let me put it up in a more clear way.

I need to check if any of the conditions is true in the table.

Column1= 13*

AND column2 IN (1, 2, 3)

AND  column2 not IN (4, 5, 6)

AND  ChargedAmount  > 20
OR

Column1= 13* AND
column2 IN (1, 2, 3)

AND column2 IN (4, 5, 6)

AND ChargedAmount  > 100

Can you help in coding the above specification?              

---

And to reiterate: Show the exact code that you are trying that "does not work".

I suspect that you have a serious problem because

column1=13* is going to throw all sorts of errors. If column1 is numeric then the you will get a message about attempting to convert "13*" to a numeric value for comparison and failing with invalid values. If Column1 is character then you might get a comparison but I am not going to even try to test it without your data. Character values really should be in quotes:  ="13*"

As another main point of logic you likely need to group the values if you need all of those things separated by OR to evaluate as true:

(
Column1= 13*

AND column2 IN (1, 2, 3)

AND  column2 not IN (4, 5, 6)

AND  ChargedAmount  > 20
)
OR
(
Column1= 13* AND
column2 IN (1, 2, 3)

AND column2 IN (4, 5, 6)

AND ChargedAmount  > 100
)

But that will fail because column2 cannot ever be in (1 2 3) and (4 5 6) at the same time. Ever. Really. So you still need to post example data in the form of a data step and the actual code you are attempting.

[Tom]

Your second condition still does not make any sense.  COLUMN2 cannot be both between 1 and 3 and between 4 and 6 at the same time.

POST DATA.  Make it up. Type it in.   Put it in the form of a data step that anyone can run to create a dataset that they can use to test answers. Something like:

data have;
  input id column1 column2 chargedAmount ;
cards;
1 13 1 0
1 13 1 25
1 13 1 125
2 13 4 0
2 13 4 25
2 13 4 125
;

 Figure out what as the answer and do the same.

[Divya12]

I need to check if the member has a particular claim(COL) (1,2,3)  but should not contain sub claim (4,5,6)

So first condition:

Billtype contains 13%

and Col in (1, 2, 3)

but Col not in (4,5,6)

and the chargedamount>20

So the first condition would not be satisfied because we need only members who have col(1,2,3) but not (4,5,6) within the subquery 

Second Condition:

Billtype contains 13%

and Col in (1, 2, 3)

and Col in (4,5,6)

and the chargedamount>100

The second condition will be satisfied as Col contains (1,2,3) and also (4,5,6) and charged amount>100 

INPUT:

client id	Order ID	billtype	col	Chargedamount
1	1	138	1	20
1	2	138	4	5
1	3	137	2	10
1	4	136	3	25
1	5	139	5	150
1	6	137	6	700

OUTPUT:

1	5	139	5	150
1	6	137	6	700

[Tom]

Why are you outputting only the last two observations?  What was the criteria that eliminated the other rows?

If the test for 1,2,3 or 4,5,6 is supposed to go across observations I would think you would either keep all of the observations for a CLIENT_ID or none of them.

Posting a report/spreadsheet/table is better than no data, but a dataset is better still.  Looking at your report we cannot tell if BILLTYPE is numeric or character.  Let's assume only COL and CHARGEDAMOUNT are numbers and the others a characters.

data have ;
  input client_id $ Order_ID $ billtype $ col Chargedamount;
cards;
1 1 138 1 20
1 2 138 4 5
1 3 137 2 10
1 4 136 3 25
1 5 139 5 150
1 6 137 6 700
;

[Divya12]

Because the Col has (1,2,3) and col (4,5,6) and having charged Amount >100

[Tom]

---

@Divya12 wrote:
Because the Col has (1,2,3) and col (4,5,6) and having charged Amount >100

---

No.  For ORDERID 5 and 6 the value of COL is in 4 to 6.  An individual value cannot be both <4 and >3 at the same.

So you want to use OR instead of AND there.  Or just use one IN operator with the full list of possible values.

[Tom]

Are you trying to do this:

data have ;
  input client_id $ Order_ID $ billtype $ col Chargedamount;
cards;
1 1 138 1 20
1 2 138 4 5
1 3 137 2 10
1 4 136 3 25
1 5 139 5 150
1 6 137 6 700
;

data test1;
  set have;
  rule1 =(billtype =: '13') and (col in (1:3)) and (chargedamount>20) ;
  rule2 =(billtype =: '13') and (col in (1:6)) and (chargedamount>100) ;
run;

proc print;
run;

       client_
Obs      id       Order_ID    billtype    col    Chargedamount    rule1    rule2

 1        1          1          138        1           20           0        0
 2        1          2          138        4            5           0        0
 3        1          3          137        2           10           0        0
 4        1          4          136        3           25           1        0
 5        1          5          139        5          150           0        1
 6        1          6          137        6          700           0        1

In which case it looks like the result of your second rule only is what you want from this set of example data.

[Divya12]

For example.

I am a member who is having a condition col(1,2,3) but i should not have condition col(4,5,6) and charged amt>25..

I am a member who is having conditions(1,2,3) and also conditions (4,5,6) and my chargedamount>100

[Tom]

---

@Divya12 wrote:
For example.

I am a member who is having a condition col(1,2,3) but i should not have condition col(4,5,6) and charged amt>25..

I am a member who is having conditions(1,2,3) and also conditions (4,5,6) and my chargedamount>100

---

So some concrete examples.  Show examples of members that have data such that you want to include that member and examples with values that mean you want to exclude that member.  Also be clear about what you want the result to be.  You previous example the output looked like the input but only some of the observations were selected, even though all of the observations were for the same CLIENT_ID (which I assume means MEMBER).