04-03-2018 11:08 PM - edited 04-03-2018 11:09 PM
how do you convert the following sql into SAS?
delete from #table1
from #table1 a, #table2 b
where a.nbid = b.nbid
and a.campcode = b.Campcode
and a.trandate = b.trandate
and b.nbid_static is null
and A.Customer = 'TgtCust'
and A.Period = 'TgtPeriod'
and A.campctrl is null
04-04-2018 12:38 AM
data want; merge table1 (keep=_ALL_ where=(cautomer='TgtCost' and Period='TgtPeriod' and campctrl is missing)) table2 (keep=nbid Campcode trandate nbid_static where=(nbid_static is missing)); by nbid campcode trandate; run;
04-04-2018 12:48 AM
04-04-2018 12:56 AM
Just a guess, since I don't know that SQL dialect, Try:
delete from table1 as A where Customer = 'TgtCust' and Period = 'TgtPeriod' and campctrl is null and exists (select * from table2 where nbid_static is null and nbid=A.nbid and campcode=A.campcode and trandate=A.trandate);
04-04-2018 01:29 AM - edited 04-04-2018 01:30 AM
The simplest would be something like:
data want; merge table1 (in=A) table2 (in=B keep=nbid Campcode trandate nbid_static where=(nbid_static is missing)); by nbid campcode trandate; if A; if B and Customer='TgtCust' and Period='TgtPeriod' and missing(campctrl) then delete; drop nbid_static; run;
provided the join is not many to many.