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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.