Desktop productivity for business analysts and programmers

Running Oracle sql in SAS SG Proc sql

Reply
Occasional Contributor
Posts: 13

Running Oracle sql in SAS SG Proc sql

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.

Super User
Posts: 3,233

Re: Running Oracle sql in SAS SG Proc sql

[ Edited ]

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

 

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

Occasional Contributor
Posts: 13

Re: Running Oracle sql in SAS SG Proc sql

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 

Super User
Posts: 3,233

Re: Running Oracle sql in SAS SG Proc sql

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. 

Super User
Posts: 7,422

Re: Running Oracle sql in SAS SG Proc sql


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 4 replies
  • 139 views
  • 0 likes
  • 3 in conversation