Oracle SQL script

[bob021]

Hi

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

[LinusH · Accepted solution]

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

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

[bob021]

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

[LinusH]

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

[bob021]

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

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

[jakarman]

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

[jakarman]

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.

[jakarman]

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.