topic Re: How to make NOT IN condition in SAS DI in SAS Studio

How to make NOT IN condition in SAS DI

umashankersaini — Thu, 12 Apr 2018 03:39:50 GMT

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

Re: How to make NOT IN condition in SAS DI

Shmuel — Thu, 12 Apr 2018 08:21:44 GMT

proc sql;

create table_c as

select * from table_a

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

quit;

Re: How to make NOT IN condition in SAS DI

umashankersaini — Thu, 12 Apr 2018 15:14:52 GMT

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

Re: How to make NOT IN condition in SAS DI

Shmuel — Thu, 12 Apr 2018 18:14:00 GMT

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.

Re: How to make NOT IN condition in SAS DI

LinusH — Sat, 14 Apr 2018 19:14:58 GMT

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.