BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bob021
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20

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

7 REPLIES 7
LinusH
Tourmaline | Level 20

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
bob021
Calcite | Level 5

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
Tourmaline | Level 20

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
bob021
Calcite | Level 5

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
Barite | Level 11

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

---->-- ja karman --<-----
jakarman
Barite | Level 11

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 --<-----
jakarman
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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