Programming the statistical procedures from SAS

Help with compress in Sql please

Reply
Frequent Contributor
Posts: 110

Help with compress in Sql please

Hi

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

Respected Advisor
Posts: 2,994

Re: Help with compress in Sql please

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%'

Esteemed Advisor
Posts: 6,270

Re: Help with compress in Sql please

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 2 replies
  • 312 views
  • 3 likes
  • 3 in conversation