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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.