DATA Step, Macro, Functions and more

Delete with IN command

Reply
Contributor
Posts: 58

Delete with IN command

An error is occurring when running the command below in SAS DI.

The SAS does not accept the IN command with more than one parameter?

   proc sql;

      delete from LIB_REF1.ODS_CUSTOMER

      where

         (ODS_CUSTOMER.ID1, ODS_CUSTOMER.ID2) IN (

            select CUST.ID1,CUST.ID2 from LIB_REF2.CUST

         )

   ;

Frequent Contributor
Posts: 106

Re: Delete with IN command

Hi,

Check your syntex:

proc sql;

      delete from LIB_REF1.ODS_CUSTOMER

      where

         ODS_CUSTOMER.ID1 IN (select CUST.ID1 from LIB_REF2.CUST) and

         ODS_CUSTOMER.ID2 IN (select CUST.ID2 from LIB_REF2.CUST);

quit;

Contributor
Posts: 58

Re: Delete with IN command

The proc sql that you sent does not generate the same result as the proc sql I want.

Frequent Contributor
Posts: 106

Re: Delete with IN command

can you example input and output data?

Super Contributor
Posts: 578

Re: Delete with IN command

proc sql;

delete from lib_ref1.ods_customer t1

where exists (select * from lib_ref2.cust where id1=t1.id1 and id2=t2.id2);

quit;

Contributor
Posts: 58

Re: Delete with IN command

DBailey, Thanks for the reply.

I had done this Proc SQL that you sent me.

How this Proc SQL with Exists is running very slow, I wanted to test with the IN command.

You know if I can use the IN command with more than one parameter, as my example?

   proc sql;

      delete from LIB_REF1.ODS_CUSTOMER

      where

         (ODS_CUSTOMER.ID1, ODS_CUSTOMER.ID2) IN (

            select CUST.ID1,CUST.ID2 from LIB_REF2.CUST

         )

   ;

Super User
Posts: 5,255

Re: Delete with IN command

No you can't, it's not valid ANSI SQL syntax.

It's not clear to me exactly what this SQL will leave as result. If you  can't describe in words, please supply attach sample input and output data that shows the desired logic.

Data never sleeps
Valued Guide
Posts: 2,174

Re: Delete with IN command

you have code that would run nicely in a dbms like Teradata, but is unfortunately not valid in SAS.

You could adapt it (with probable performance penalty) by concatenating the columns, like

where ODS_CUSTOMER.ID1 !! ODS_CUSTOMER.ID2 IN ( select CUST.ID1 !! CUST.ID2 from LIB_REF2.CUST  )

with implicit or explicit datatype conversion if these customer IDs are numeric.

As you use syntax typical of a dbms, your syntax will work within "explicit pass-through" to your dbms see http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a000245480.htm

Ask a Question
Discussion stats
  • 7 replies
  • 278 views
  • 3 likes
  • 5 in conversation