topic Re: Delete with IN command in SAS Programming

Delete with IN command

DavidCaliman — Fri, 17 Jan 2014 16:28:49 GMT

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

)

;

Re: Delete with IN command

pradeepalankar — Fri, 17 Jan 2014 20:56:53 GMT

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;

Re: Delete with IN command

DavidCaliman — Fri, 17 Jan 2014 21:43:04 GMT

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

Re: Delete with IN command

DBailey — Fri, 17 Jan 2014 21:46:52 GMT

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;

Re: Delete with IN command

pradeepalankar — Fri, 17 Jan 2014 21:48:42 GMT

can you example input and output data?

Re: Delete with IN command

DavidCaliman — Mon, 20 Jan 2014 12:16:55 GMT

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

)

;

Re: Delete with IN command

LinusH — Mon, 20 Jan 2014 12:56:33 GMT

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.

Re: Delete with IN command

Peter_C — Wed, 22 Jan 2014 08:34:13 GMT

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