topic Oracle SQL script in SAS Procedures

Oracle SQL script

bob021 — Wed, 04 Sep 2013 08:02:33 GMT

Is it possible to run script like one below in SAS (9.1) without adjusting to SAS SQL language?

SELECT TO_CHAR (reg_date, 'YYYYIW') yyyyiw,

model_no,

material_no,

MAX (wtd) wtd,

MAX (mtd) mtd,

MAX (ytd) ytd

FROM (SELECT sr.reg_date,

sr.part_no model_no,

ip.type_designation material_no,

COUNT (1) OVER (PARTITION BY TO_CHAR (sr.reg_date, 'YYYYIW'), sr.part_no)

wtd,

COUNT (1) OVER (PARTITION BY TO_CHAR (sr.reg_date, 'YYYYMM'), sr.part_no)

mtd,

COUNT (1) OVER (PARTITION BY TO_CHAR (sr.reg_date, 'YYYY'), sr.part_no)

ytd

FROM amb.service sr

INNER JOIN

amb.part ip

ON ip.contract = sr.part_contract

AND ip.part_no = sr.part_no

WHERE sr.part_contract = '12506'

AND sr.agreement_id <> 'PACEF12506'

AND sr.state <> 'New')

WHERE TO_CHAR (reg_date, 'YYYYIW') = TO_CHAR (SYSDATE-2, 'YYYYIW')

GROUP BY TO_CHAR (reg_date, 'YYYYIW'), model_no, material_no;

Thanks

Bob

Re: Oracle SQL script

LinusH — Wed, 04 Sep 2013 08:42:06 GMT

Yes, you just wrap the code in an explicit SQL pass thru block.

For details, see the SAS/ACCESS to Oracle documentation.

Re: Oracle SQL script

bob021 — Wed, 04 Sep 2013 10:39:28 GMT

Thank you very much for quick answer.

When run

PROC SETINIT NOALIAS;

RUN;

There is no interface for Oracle and when i connect trough ODBC SAS can recognize for example function OVER

Thanks

Bob

Re: Oracle SQL script

jakarman — Wed, 04 Sep 2013 11:14:19 GMT

SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Second Edition ( SQL Pass-Through Facility Specifics for Oracle)

Re: Oracle SQL script

LinusH — Wed, 04 Sep 2013 11:17:31 GMT

Hope it works. When using ODBC it's the Oracle ODBC driver and the ODBC specs (not SAS) that will limit your possibilities.

Re: Oracle SQL script

jakarman — Wed, 04 Sep 2013 11:18:05 GMT

You are using an ODBC connection? I suppose from a SAS/base Windows environment using the ODBC drivers.

SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Second Edition ( Pass-Through Facility Specifics for ODBC)

You will a lot of the normal additions specific to Oracle but this could also work.

Re: Oracle SQL script

bob021 — Wed, 04 Sep 2013 11:24:58 GMT

There was spelling error in my previous email ... can't (instead can)

Connection is working fine but ODBC SAS can't recognize function OVER

Re: Oracle SQL script

jakarman — Wed, 04 Sep 2013 11:37:13 GMT

Could make sense as the over functionality is not part of standard ODBC (I assume).

SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Second Edition (Introduction to SAS/ACCESS Interface to ODBC )

I/We are seeing a lot of RDBMS-systems adding specfic functions not being standard. Do not expect them to behave as a standard.