how do you convert the following sql into SAS?
delete from #table1
--select A.*
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
go
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;
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);
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.