topic Adding equivalent of if/then/delete in SQL join in SAS Programming

Adding equivalent of if/then/delete in SQL join

brulard — Thu, 22 Jun 2017 21:48:20 GMT

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

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

kiranv_ — Thu, 22 Jun 2017 22:15:19 GMT

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;

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

Reeza — Thu, 22 Jun 2017 22:17:32 GMT

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;