topic Re: how to modify oracle code in proc SQL in SAS Procedures

how to modify oracle code in proc SQL

sss — Tue, 08 Mar 2011 14:55:47 GMT

CREATE TABLE FMEA_BASE AS
select org, location,description,anum,pcode, FCODE,
trunc(to_char(faildate- lag(faildate,1) over (partition by assetnum order by faildate)),2) as TBF_Days,
(trunc(to_char(faildate- lag(faildate,1) over (partition by assetnum order by faildate)),2))*24 as TBF_Hrs,
(trunc(actfinish-actstart,2))*24 as TTR_Hrs

from work
where location is not null and anum is not null

order by assetnum;

========================================================
This code works f9 in Oracle DB ,but i when i m trying to run this code in sas but i am getting errors
OVER,PARTITION BY keyword are not identifying by SAS

please guide me to convert this oracle code into SAS using proc sql;

Re: how to modify oracle code in proc SQL

Doc_Duke — Tue, 08 Mar 2011 15:24:11 GMT

You need to show more of your code and log. How are you connecting to the Oracle database. LIBNAME, pass thru using Oracle, or pass thru using ODBC. Each has different characteristics. If you are using LIBNAME, then you need to use SAS SQL syntax. Pass thru to oracle should give you the most transparency though, even then, there are options to control any optimization that SAS might attempt.

Check the SAS/Access to Relational Databases and SAS/Access Companion for Oracle.

Re: how to modify oracle code in proc SQL

sss — Tue, 08 Mar 2011 15:59:48 GMT

Hey
I am connecting through ODBC to oracle DB

This is the error msg

NOTE: Due to the PRESERVE_TAB_NAMES=NO libname option setting, 954
table(s) have not been displayed/returned.
1 proc sql;
2 CREATE TABLE FMEA_BASE AS
3 select orgid,siteid , location,description,assetnum,problemcode,
3 ! FAILURECODE,
4 to_char(faildate,'MM/DD/YYYY') AS FAILDATE,
5 to_char(actstart,'MM/DD/YYYY') AS ACTSTART,
6 to_char(ACTFINISH,'MM/DD/YYYY') AS ACTFINISH,
7 trunc(to_char(faildate- lag(faildate,1) over (partition by assetnum
---- --
22 76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &,
*, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND,
BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET,
LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

7 ! trunc(to_char(faildate- lag(faildate,1) over (partition by assetnum
--
22
7 ! order by faildate)),2) as TBF_Days,
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &,
(, ), *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?,
AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN,
IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^,
^=, |, ||, ~, ~=.

8 (trunc(to_char(faildate- lag(faildate,1) over (partition by
8 ! assetnum order by faildate)),2))*24 as TBF_Hrs,
9 (trunc(actfinish-actstart,2))*24 as
9 ! TTR_Hrs,(actmatcost+actlabcost+acttoolcost) as FAILURECOST
10 from tva.workorder
11 where classstructureid is not null
12 and location is not null
13 and assetnum is not null
14 and faildate is not null
15 and actstart is not null
16 and actfinish is not null
17 order by assetnum;
18 quit;

I have read a document where i came to know that lag function cannot me used in proc sql;

Re: how to modify oracle code in proc SQL

DF — Wed, 09 Mar 2011 09:33:05 GMT

If your code is a direct extract from between PROC SQL and your QUIT; lines, then you'll need to run this code in Pass-Through mode I think.

Functions like trunc() and partition by aren't SAS functions so your code flags these as an error.

Try something from http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a003113595.htm .