proc sql & where from select connecting Oracle

[AndreaVianello]

I have in Oracle    TabA and TabB

  sql in Oracle 

select *  from TabA
where (idp, keyJ) in (select distinct id, key from TabB where flag in ('C','D') ) ;

work fine.  

in SAS 

libname ora oracle user="***" password="****" path="****";  

/** connection is ok !!**/

proc sql;
create table TabC as
select *  from   ora.TabA

  where (idp, keyJ) in (select distinct id, key from  ora.TabB where flag in ('C','D') ) ;
quit;

write ERROR 79-322: Expecting a (.  

  Why ? 

thank you

[jimbarbour]

You need to use an explicit pass through type query.  Otherwise, you must use SAS syntax, which you don't want.

It would look something like this:

	PROC	SQL;
		CONNECT	TO	Oracle	AS	Cnx	("&Connection_Parameters");

		CREATE	TABLE	WORK.MyTable	AS
			SELECT	*	FROM	CONNECTION	TO	Cnx
				(SELECT	*	FROM	&Schema..&Table
				WHERE	&Where
							);

		DISCONNECT	FROM	Cnx;
	QUIT;

In your specific case, it might look something like the below.  I'm assuming that you have the Oracle driver and SAS Access for Oracle product installed.  ODBC would be another way to connect.

	PROC	SQL;
		CONNECT	TO	Oracle	AS	Ora	(user="***" password="****" path="****");

		CREATE	TABLE	WORK.MyTable	AS
			SELECT	*	FROM	CONNECTION	TO	Ora
				(select *  from TabA
					where (idp, keyJ) in (select distinct id, key from TabB where flag in ('C','D')) );

		DISCONNECT	FROM	Ora;
	QUIT;

Jim

[AndreaVianello]

thank you

[Reeza · Accepted solution]

where (idp, keyJ) in (select distinct id, key from ora.TabB where flag in ('C','D') ) ;

This is not valid SAS SQL, only Oracle so you need to convert that portion to a join.

[AndreaVianello]

thank you !