Help using Base SAS procedures

Oracle SQL script

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

Oracle SQL script

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


Accepted Solutions
Solution
‎09-04-2013 04:42 AM
Super User
Posts: 5,437

Re: Oracle SQL script

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

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

Data never sleeps

View solution in original post


All Replies
Solution
‎09-04-2013 04:42 AM
Super User
Posts: 5,437

Re: Oracle SQL script

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

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

Data never sleeps
Contributor
Posts: 60

Re: Oracle SQL script

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

Super User
Posts: 5,437

Re: Oracle SQL script

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

Data never sleeps
Contributor
Posts: 60

Re: Oracle SQL script

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

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

Trusted Advisor
Posts: 3,215

Re: Oracle SQL script

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

---->-- ja karman --<-----
Trusted Advisor
Posts: 3,215

Re: Oracle SQL script

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.

---->-- ja karman --<-----
Trusted Advisor
Posts: 3,215

Re: Oracle SQL script

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.

---->-- ja karman --<-----
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 336 views
  • 6 likes
  • 3 in conversation