How to make NOT IN condition in SAS DI

[umashankersaini]

Hi Team,

Kindly let me know how to create sql not in sub query in SAS DI Studio.

For example we have table A ( with Saving A/C) and Table B ( Loan A/C with Saving A/C) and we want to create a table which have account numbers containing only saving A/C.

Table A	Table B	Table C
121	123	121
122	126	122
123	128	125
124	124	127
125	120	129
126
127
128
129
120

Query need to develop :

Create table C as Select * from A where ID not in ( select ID from B);

I have already tried with sql join transformation which includes sub-query in join condition with NOT IN but due to join (Inner join), It is providing Cartesian product.

Kindly suggest any other way or am i missing something.

Regards,

Uma Shanker Saini

[Shmuel]

proc sql;

    create table_c as 

    select * from table_a

    where (cust not in (select cust from table_b));

quit;

[umashankersaini]

Hi Shmuel,

Thanks for your quick reply but have you read the question properly ??

If you are unable to understand the question then please let me know i could explain in more lay man language.

Regards,

Uma Shanker Saini

[Shmuel]

Please post your log. I cannot imagine cartesian join with sub-query of ID NOT IN table_B

even if ID is not unique key in the table.

May be there is another issue.

[LinusH]

Sub-queries are a bit tricky in DI studio.
But you need to get rid of that join, and only keep the sub query. I believe you can do it locations of SQL Join transformation.