topic Help with compress in Sql please in Statistical Procedures

Help with compress in Sql please

ammarhm — Tue, 09 Jun 2015 22:37:36 GMT

Could anyone please tell me how to correct this code? the problem seems to be in the "compress" part of the sql

proc sql;

connect to oracle (user=xxx path='xxx' orapw="xxx");

create table Want as

select

id,

place,

date

from connection to oracle

(

select

a.id,

a.place,

a.date

from T1 a inner join Tq b

on a.rec=b.rec and a.place=b.place

where

(

b.place='Tx' and ( substr(compress(b.diagnosis,,'p'),1,3) = 'NYC' )

)

);

quit;

It seems sql/oracle dose not accept the compress theres, any ideas on how to solve this please?

Best regards

Re: Help with compress in Sql please

SASKiwi — Wed, 10 Jun 2015 01:01:52 GMT

You are using SQL passthru so your SQL must conform to the Oracle dialect. COMPRESS is a SAS function not valid in Oracle. There is a SUBSTR function in Oracle but it may not work exactly the same as SAS's.

I don't have Oracle to test on but I would check out the Oracle LIKE operator:

and b.diagnosis like 'NYC%'

Re: Help with compress in Sql please

Kurt_Bremser — Wed, 10 Jun 2015 05:51:45 GMT

Since everything in the "connection to oracle" is executed by the RDBMS, you best get help from your Oracle admins; they know best how to solve your problem in Oracle.