09-04-2013 04:02 AM
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,
MAX (wtd) wtd,
MAX (mtd) mtd,
MAX (ytd) ytd
FROM (SELECT sr.reg_date,
COUNT (1) OVER (PARTITION BY TO_CHAR (sr.reg_date, 'YYYYIW'), sr.part_no)
COUNT (1) OVER (PARTITION BY TO_CHAR (sr.reg_date, 'YYYYMM'), sr.part_no)
COUNT (1) OVER (PARTITION BY TO_CHAR (sr.reg_date, 'YYYY'), sr.part_no)
FROM amb.service sr
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;
09-04-2013 06:39 AM
Thank you very much for quick answer.
PROC SETINIT NOALIAS;
There is no interface for Oracle and when i connect trough ODBC SAS can recognize for example function OVER
09-04-2013 07:18 AM
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.
09-04-2013 07:37 AM
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.
Need further help from the community? Please ask a new question.