Adding equivalent of if/then/delete in SQL join

[brulard]

hi,

I have a criteria that relies on the output from three joins to be able to execute. So currently, I perform my 3 joins in sql, then add a base sas statement as such:

data want;set have;

If product in ('MC1', 'MC2') and Status='CLOSED' and balance='' then delete;run;

Here is an example of current sql join, if someone has a tip to modify it to add the above?

proc sql; create table final as; 

select *

from a left join status_table b 

on id=id

inner join balance_table c

on id=id;QUIT;

(first table A contains PRODUCT) , thanks

[kiranv_]

may be something like this. I have not tested this

proc sql;

create table final as

select * from 

(select *

from a left join status_table b 

on id=id

inner join balance_table c

on id=id)a

where not(product in ('MC1', 'MC2') and Status='CLOSED' and balance='' );

quit;

[Reeza]

Didn't test it but you should be able to just add a WHERE to your query.

proc sql;
create table final as 
select *
from a 
left join status_table b 
on id=id
inner join balance_table c
on id=id
WHERE not (product in ('MC1', 'MC2') and Status='CLOSED' and balance='');

QUIT;