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
Yes, you just wrap the code in an explicit SQL pass thru block.
For details, see the SAS/ACCESS to Oracle documentation.
Yes, you just wrap the code in an explicit SQL pass thru block.
For details, see the SAS/ACCESS to Oracle documentation.
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
Hope it works. When using ODBC it's the Oracle ODBC driver and the ODBC specs (not SAS) that will limit your possibilities.
There was spelling error in my previous email ... can't (instead can)
Connection is working fine but ODBC SAS can't recognize function OVER
SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Second Edition ( SQL Pass-Through Facility Specifics for Oracle)
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.
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.
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.
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.