topic proc sql & where from select connecting Oracle in SAS Programming

proc sql & where from select connecting Oracle

AndreaVianello — Thu, 05 Aug 2021 14:59:45 GMT

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

Re: proc sql & where from select connecting Oracle

jimbarbour — Thu, 05 Aug 2021 15:26:44 GMT

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

Re: proc sql & where from select connecting Oracle

Reeza — Thu, 05 Aug 2021 15:26:45 GMT

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.

Re: proc sql & where from select connecting Oracle

AndreaVianello — Fri, 06 Aug 2021 08:01:45 GMT

thank you !

Re: proc sql & where from select connecting Oracle

AndreaVianello — Fri, 06 Aug 2021 08:01:53 GMT

thank you