DATA Step, Macro, Functions and more

sql delete

Reply
New Contributor
Posts: 2

sql delete

[ Edited ]

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

 

Trusted Advisor
Posts: 1,827

Re: sql delete

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;
Super User
Posts: 5,829

Re: sql delete

Since this is a SAS forum you can't expect us to u understand the syntax from other environments.
Basically, you probably need to replace the - with a WHERE EXISTS conditions.
For other differences, you should be able to detect by studying documentation and try running your code.
Data never sleeps
Esteemed Advisor
Posts: 5,408

Re: sql delete

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);
PG
PROC Star
Posts: 2,231

Re: sql delete

[ Edited ]

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. 

Ask a Question
Discussion stats
  • 4 replies
  • 114 views
  • 1 like
  • 5 in conversation