BookmarkSubscribeRSS Feed
DavidCaliman
Calcite | Level 5

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

         )

   ;

7 REPLIES 7
pradeepalankar
Obsidian | Level 7

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;

DavidCaliman
Calcite | Level 5

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

pradeepalankar
Obsidian | Level 7

can you example input and output data?

DBailey
Lapis Lazuli | Level 10

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;

DavidCaliman
Calcite | Level 5

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

         )

   ;

LinusH
Tourmaline | Level 20

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
Peter_C
Rhodochrosite | Level 12

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1299 views
  • 3 likes
  • 5 in conversation