topic Re: sql delete in SAS Programming

sql delete

galenhew — Wed, 04 Apr 2018 03:09:18 GMT

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

Re: sql delete

Shmuel — Wed, 04 Apr 2018 04:38:10 GMT

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;

Re: sql delete

LinusH — Wed, 04 Apr 2018 04:48:43 GMT

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.

Re: sql delete

PGStats — Wed, 04 Apr 2018 04:56:10 GMT

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);

Re: sql delete

ChrisNZ — Wed, 04 Apr 2018 05:30:31 GMT

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.