topic Re: Error with passthrough to oracle in SAS Programming

Error with passthrough to oracle

ammarhm — Wed, 19 Dec 2018 02:16:41 GMT

Hi everyone

I am using the following code to connect ot oracle:

proc sql;
connect using datax as oracle;
create table combined_user as
select 
	*
	
from connection to oracle
	(
	select distinct 
		*
	from datax.dbo.process a left join datax.dbo.users b 
	  on a.id=b.id and a.location=b.location
	);
quit;

This is working fine without issues.

Due to problems with id coding, I needed to remove some punctuations in the column id from the first table. i tried the following



proc sql;
connect using datax as oracle;
create table combined_user as
select 
	*
	
from connection to oracle
	(
	select distinct 
		*
	from datax.dbo.process a left join datax.dbo.users b 
	  on compress(a.id,,'p')=b.id and a.location=b.location
	);
quit;

which generated an error, possibly because oracle does not understand the command compress.

Any suggestions on how to solve this? I tried reg_replace but with no luck

Another question, is there anything in oracle that can do something similar to matching by "like"?

	(
	select distinct 
		*
	from datax.dbo.process a left join datax.dbo.users b 
	  on compress(a.id,,'p') like b.id and a.location=b.location
	);
quit;

Kind regards

Re: Error with passthrough to oracle

r_behata — Wed, 19 Dec 2018 02:30:53 GMT

Your question has the answer in it. You cannot use the sas functions inside the SQL Pass-thru query.

You should perhaps use the Oracle equivalent for compress which is TRIM , which removes the leading and trailing blanks and you can also add the punctuation to the list of characters to the TRIM function which you are trying to remove in the query.

Re: Error with passthrough to oracle

ChrisNZ — Wed, 19 Dec 2018 02:45:59 GMT

You can't use SAS function in an Oracle query, just like you can't use Oracle functions in a SAS query.

replace(translate(ID, '.,-:+*/;', '#'), '#', '')

does what you want.

Re: Error with passthrough to oracle

ChrisNZ — Wed, 19 Dec 2018 02:46:47 GMT

regexp_replace(ID, '[-.,:+*/;]')

works too.

Re: Error with passthrough to oracle

ChrisNZ — Wed, 19 Dec 2018 02:54:02 GMT

proc sql;
connect using datax as oracle;
create table combined_user as
select *
from connection to oracle
(
select distinct *
from datax.dbo.process a left join datax.dbo.users b
on compress(a.id,,'p')=b.id and a.location=b.location
)
order by 1;
quit;

Just a reminder:

The code in blue runs in SAS and requires SAS syntax.

The code in red runs in Oracle and requires Oracle syntax.

Re: Error with passthrough to oracle

ammarhm — Wed, 19 Dec 2018 05:17:56 GMT

Thanks all,

Any suggestions about using LIKE with wilde card % in Oracle?