Adding equivalent of if/then/delete in SQL join

Reply
Frequent Contributor
Posts: 84

Adding equivalent of if/then/delete in SQL join

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

 

PROC Star
Posts: 253

Re: Adding equivalent of if/then/delete in SQL join

[ Edited ]

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;

Super User
Posts: 17,899

Re: Adding equivalent of if/then/delete in SQL join

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;
Ask a Question
Discussion stats
  • 2 replies
  • 107 views
  • 0 likes
  • 3 in conversation