BookmarkSubscribeRSS Feed
hk186002
Calcite | Level 5

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.

4 REPLIES 4
SASKiwi
PROC Star

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

 

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

hk186002
Calcite | Level 5

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 

SASKiwi
PROC Star

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. 

Kurt_Bremser
Super User

@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.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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