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
)
;
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;
The proc sql that you sent does not generate the same result as the proc sql I want.
can you example input and output data?
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;
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
)
;
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.