BookmarkSubscribeRSS Feed
sss
Fluorite | Level 6 sss
Fluorite | Level 6
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;
3 REPLIES 3
Doc_Duke
Rhodochrosite | Level 12
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.
sss
Fluorite | Level 6 sss
Fluorite | Level 6
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;
DF
Fluorite | Level 6 DF
Fluorite | Level 6
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 .

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
  • 3 replies
  • 1482 views
  • 0 likes
  • 3 in conversation