Running Oracle sql in SAS SG Proc sql

hk186002 — Tue, 27 Jun 2017 00:40:53 GMT

Hi Gurus,

I tried to run the below oracle query in SAS EG under proc SQL but I am getting the error as shown below. The same query runs fine in oracle database but here I am getting issues. Tried adjusting the brackets too but still no goo. Can any one help here




LIBNAME CPS			ORACLE USER = user PASSWORD = pass PATH='cpsprd_ro' SCHEMA = 'CPS_OWNER';
LIBNAME MIS			ORACLE USER = user PASSWORD = pass PATH='cpsprd_ro' SCHEMA = 'MIS_OWNER';


proc sql;
 connect to oracle (user=user password=pass path='cpsprd_ro'  readbuff=100000);
create table want as select * 
 from connection to oracle
(

 
select

base.dt, base.usr_id, base.id,base.USER_NAME,base.name,

COALESCE(GROSS_HOURS,0) as GROSS_HOURS,

COALESCE(FLEX_ADJ,0)as FLEX_ADJ,

COALESCE(OVERTIME,0) as OVERTIME,

COALESCE(BORROWED,0) as BORROWED,

COALESCE(ONLOAN,0) as ONLOAN,

COALESCE(HOLYDAYS,0) as HOLIDAYS,

COALESCE(SICKNESS,0) as SICKNESS,

COALESCE(SPECIAL_JOB,0) as SPECIAL_JOB,

COALESCE(MGNT_ADJ,0) as MGNT_ADJ,

COALESCE(TRAINING,0) as TRAINING,

COALESCE(HOURS_ADJUSTMENT,0)as HOURS_ADJ,

COALESCE(COMPLETED_HOURS,0)as COMPLETED_HOURS,

COALESCE(COMPLETED_FOLDERS,0)as COMPLETED_FOLDERS,

base.BRL_ID

 

 

FROM(

SELECT dt, t2.UGP_ID,t2.USR_ID,t2.GRP_ID,t2.NAME,t2.BRL_ID, t2.ID, t2.user_name

FROM (

select TRUNC(SYSDATE,'y')-1+ROWNUM dt

from all_objects 

where ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y')

)dtz

CROSS JOIN

(

select UG.UGP_ID,UG.USR_ID,UG.GRP_ID,CG.NAME,CG.BRL_ID,USR.ID, USR.NAME as USER_NAME

from CPS.CPS_USER_GROUP UG

left join CPS.CPS_GROUP CG




on UG.GRP_ID = CG.GRP_ID

left join CPS.CPS_USER USR

on UG.USR_ID = USR.USR_ID

-- when you need to add the new team, you need to add here

-- updated 24/05 to include settlement team

where CG.BRL_ID in (1,16) and CG.NAME in ('DOCUMENTATION','DOC MANAGEMENT','DISBURSALS TEAM',

           'SUPPORT SERVICES TEAM','VERIFICATIONS','HOUSING LOANS VALIDATIONS','COMPLEX - SECURITIES','DOC PREP','SETTLEMENTS',

           'NATIONAL EXAMINATIONS','DISBURSALS','FBI','LEG-ENDS','PHONE TEAM','RECORDS MAN','THE UNTOUCHABLES',

           'MAVERICKS','DREAM TEAM','NINJA TURTLES','TEAM ELITE','TOP GUNS','TRANSFORMERS','CMS NINJA TURTLES','DPS DISCHARGE','DPS EXAMINATIONS')or CG.NAME like '%HLT%'

           or CG.NAME like '%LEVEL 23%'

           )t2

)base

LEFT JOIN MIS.MIS_USER_SUMMARY t1    

on t1.USR_ID = base.USR_ID and t1.action_date = base.dt and t1.brl_id = base.brl_id

 

LEFT JOIN

(

select adj.dt, adj.usr_id,

sum(adj.flex_adj)as FLEX_ADJ,

sum(adj.overtime)as OVERTIME,

sum(adj.BORROWED) as BORROWED,

sum(adj.ONLOAN) as ONLOAN,

sum(adj.HOLIDAYS) as HOLYDAYS,

sum(adj.SICKNESS) as SICKNESS,

sum(adj.SPECIAL_JOB) as SPECIAL_JOB,

sum(adj.MGNT_ADJ) as MGNT_ADJ,

sum(adj.TRAINING) as TRAINING

FROM(

    select

    t4.DAY as dt,

    t4.USR_ID,

    case when hat_id=1 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as FLEX_ADJ,

    case when hat_id=2 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as OVERTIME,

    case when hat_id=3 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as BORROWED,

    case when hat_id=7 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as ONLOAN,

    case when hat_id=4 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as HOLIDAYS,

    case when hat_id=6 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as SICKNESS,

    case when hat_id=8 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as SPECIAL_JOB,

    case when hat_id=9 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as MGNT_ADJ,

    case when hat_id=5 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as TRAINING

    from MIS.MIS_USER_WORK_ADJUSTMENTS t4

    where t4.DAY>='25-JUL-2016'

) adj

GROUP BY adj.dt, adj.usr_id 

)adj1

on adj1.USR_ID = base.USR_ID and adj1.dt = base.dt

where base.dt >='19-JUN-2017' and base.dt<sysdate+21  -- filter to update the date range

order by base.usr_id


);
   disconnect from oracle;
quit;

-------------------ERROR----------------------------------
 _
 22
 200
ERROR 22-322: Syntax error, expecting one of the following: a name, ;, ',', ANSIMISS, AS, CROSS, EXCEPT, FULL, GROUP, HAVING, 
 INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT, UNION, WHERE. 

ERROR 200-322: The symbol is not recognized and will be ignored.

Re: Running Oracle sql in SAS SG Proc sql

SASKiwi — Tue, 27 Jun 2017 02:31:22 GMT

Try removing comments starting with two dashes (--). They don't work in SAS.

Comments can be done with: /* My comment */

Re: Running Oracle sql in SAS SG Proc sql

hk186002 — Tue, 27 Jun 2017 02:41:59 GMT

Hi SasKiwi,

Thanks for your reply.

I have tried removing comments but still the same error and also I tried to remove the bracket at the end but stil no go. I have attached the log too.

Regards,

Hari

Re: Running Oracle sql in SAS SG Proc sql

SASKiwi — Tue, 27 Jun 2017 04:30:59 GMT

I suggest you copy the Oracle part of the query back into Oracle and see if it is still working. Most likely you have a problem with commas or unbalanced brackets and you are in the best position to test your code.

BTW your LIBNAMEs at the start are redundant as they are not used in a passthrough query.

Re: Running Oracle sql in SAS SG Proc sql

Kurt_Bremser — Tue, 27 Jun 2017 07:36:08 GMT

@hk186002 wrote:

Hi SasKiwi,

Thanks for your reply.

I have tried removing comments but still the same error and also I tried to remove the bracket at the end but stil no go. I have attached the log too.

Regards,

Hari

In that code, you have one closing bracket too much. Let the SAS enhanced editor help you, as it shows matching brackets when the cursor is placed before an opening or after a closing bracket.

topic Re: Running Oracle sql in SAS SG Proc sql in SAS Enterprise Guide

Running Oracle sql in SAS SG Proc sql

Re: Running Oracle sql in SAS SG Proc sql

Re: Running Oracle sql in SAS SG Proc sql

Re: Running Oracle sql in SAS SG Proc sql

Re: Running Oracle sql in SAS SG Proc sql