HI Trying to run the code below but keep on getting an error
ERROR: ORACLE prepare error: ORA-00933: SQL command not properly ended.
Can you help me figure out why ?
See code below:
PROC SQL;
CONNECT TO ORACLE (PATH = COLLP USER = &USERID. PASSWORD = &PWD.);
CREATE TABLE mytable AS
SELECT * FROM CONNECTION TO ORACLE (
SELECT
lf.list_name,
lf.priority,
wk.call_id,
wk.parent_call_id,
dl.mds_rec_id,
dl.acct_contact_key,
dl.system_no,
dl.principle_num,
dl.file_date,
dl.DWNLD_DT,
CASE WHEN ex.mds_field_value IS NULL THEN 'N' ELSE 'Y' END as Excluded_Account_Flag,
CASE WHEN exZip.mds_field_value IS NULL THEN 'N' ELSE 'Y' END as Excluded_Zip_Flag,
CASE WHEN dl.days_delq <= '029' THEN '1'
WHEN dl.days_delq <= '059' THEN '2'
WHEN dl.days_delq <= '089' THEN '3'
WHEN dl.days_delq <= '119' THEN '4'
WHEN dl.days_delq <= '149' THEN '5'
WHEN dl.days_delq <= '179' THEN '6'
WHEN dl.days_delq <= '209' THEN '7'
ELSE '0' END as Due_Stage,
wk.sso_id,
dl.min_tz ,
dl.max_tz,
wk.mds_status,
wk.mds_term_cd,
acl.ACTION_DESC as ACTION_DESC,
SUM(acl.ATTEMPT_CNT) as ATTEMPT_CNT,
SUM(acl.CALL_CNT) as CALL_CNT,
SUM(acl.CONNECT_CNT) as CONNECT_CNT,
SUM(acl.CONTACT_CNT) as CONTACT_CNT,
SUM(acl.CUST_CALL_CNT) as CUST_CALL_CNT,
SUM(acl.FIND_CNT) as FIND_CNT,
SUM(acl.NO_PROMISE_CNT) as NO_PROMISE_CNT,
SUM(acl.PROMISE_CNT) as PROMISE_CNT,
SUM(acl.RPC_CNT) as RPC_CNT,
COUNT(DISTINCT CASE WHEN
dl.HOME_PH = '0000000000'
AND dl.MOBILE_NUMBER = '0000000000'
AND dl.WORK_PH = '0000000000'
AND dl.FAX_PH = '0000000000'
AND dl.PAGER_PH = '0000000000'
THEN dl.mds_rec_id ELSE NULL END) as No_Phone_Accounts,
COUNT(DISTINCT CASE WHEN wk.mds_term_cd = 'ML' THEN wk.mds_rec_id ELSE NULL END) as Just_Looking,
SUM(dl.mds_acct_status) as mds_acct_status_attempts
FROM mpoc.dbo.MDS_LOAD_FACT dl
LEFT JOIN MDS_EXCLUSION_FACT ex on dl.acct_contact_key = ex.mds_field_value
and ex.header_date = dl.header_date
LEFT JOIN MDS_EXCLUSION_FACT exZip on dl.zip = exZip.mds_field_value
and exZip.header_date = dl.header_date
LEFT JOIN MDS_ACTV_WKST_FACT wk on dl.acct_contact_key = wk.acct_contact_key
and dl.header_date = wk.header_date
LEFT JOIN dbo.action_cd_lkup acl on wk.mds_term_cd = acl.action_cd
LEFT JOIN MDS_LISTS_PRIORITY_FACT lf on lf.acct_contact_key = dl.acct_contact_key
and lf.header_date = dl.header_date
where dl.file_dt >='01Jan2019'd and dl.file_dt<='28Feb2019'd);
QUIT;
I changed the date and still getting the error .What else would be ?Thank you
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.