The SAS Output Delivery System and reporting techniques

missing right parenthesis in PROC SQL

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

missing right parenthesis in PROC SQL

I am trying to extract data between a static date (01012014) and last day of the previous month

 

This is my code:

 

%let ostart='01012014';
%let oend = put(intnx('month',' today()', -1, 'end'), mmddyy10.);

proc sql;
connect to odbc as ods
(datasrc='*********' user=------ password=------);

create table elig_facets as
select *
from connection to ods
(select edw_cmc_sbsb_subsc.sbsb_employ_id,
edw_cmc_mepe_prcs_elig.mepe_term_dt
from edw_cmc_sbsb_subsc,
edw_cmc_mepe_prcs_elig,
edw_cmc_pdds_prod_desc,
edw_cmc_meme_member
where edw_cmc_mepe_prcs_elig.pdpd_id=edw_cmc_pdds_prod_desc.pdpd_id
and edw_cmc_mepe_prcs_elig.meme_ck=edw_cmc_meme_member.meme_ck
and edw_cmc_meme_member.sbsb_ck=edw_cmc_sbsb_subsc.sbsb_ck
and rtrim(edw_cmc_pdds_prod_desc.php_product_type_desc) in ('centennial care')
and edw_cmc_mepe_prcs_elig.mepe_eff_dt_shrt between to_date ("&ostart"d, 'ddmmyyyy') and to_date ("&oend"d, 'ddmmyyyy')
and edw_cmc_mepe_prcs_elig.mepe_elig_ind in ('y')
)
;
disconnect from ods;
quit;

 

 

The error is :

 

42 and rtrim(edw_cmc_pdds_prod_desc.php_product_type_desc) in ('centennial care')
43 and edw_cmc_mepe_prcs_elig.mepe_eff_dt_shrt between to_date ("&ostart"d, 'ddmmyyyy') and to_date ("&oend"d,
43 ! 'ddmmyyyy')
44 and edw_cmc_mepe_prcs_elig.mepe_elig_ind in ('y')
45 )
46 ;
ERROR: CLI describe error: [Oracle][ODBC][Ora]ORA-00907: missing right parenthesis
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
47 disconnect from ods;
NOTE: Statement not executed due to NOEXEC option.
48 quit;

 

Please help me with the code and thanks in advance


Accepted Solutions
Solution
‎04-06-2016 01:43 PM
Respected Advisor
Posts: 3,063

Re: missing right parenthesis in PROC SQL

[ Edited ]

 This should get you closer to a solution. I'm not sure if double quotes will work in your SQL. Try double quotes with a hardcoded valid date that works with single quotes. If it fails then only single quotes are allowed. You can wrap a SAS macro variable in single quotes like this: %str(')&ostart%str(') and it will resolve OK.

 

%let ostart= 01012014;
%let oend = %sysfunc(intnx(month, today(), -1, end), mmddyy10.);
proc sql;
connect to odbc as ods
(datasrc='*********' user=------ password=------);
create table elig_facets as
select *
from connection to ods
(select edw_cmc_sbsb_subsc.sbsb_employ_id,
edw_cmc_mepe_prcs_elig.mepe_term_dt
from edw_cmc_sbsb_subsc,
edw_cmc_mepe_prcs_elig,
edw_cmc_pdds_prod_desc,
edw_cmc_meme_member
where edw_cmc_mepe_prcs_elig.pdpd_id=edw_cmc_pdds_prod_desc.pdpd_id
and edw_cmc_mepe_prcs_elig.meme_ck=edw_cmc_meme_member.meme_ck
and edw_cmc_meme_member.sbsb_ck=edw_cmc_sbsb_subsc.sbsb_ck
and rtrim(edw_cmc_pdds_prod_desc.php_product_type_desc) in ('centennial care')
and edw_cmc_mepe_prcs_elig.mepe_eff_dt_shrt between to_date ("&ostart", 'ddmmyyyy') and to_date ("&oend", 'ddmmyyyy')
and edw_cmc_mepe_prcs_elig.mepe_elig_ind in ('y')
)
;
disconnect from ods;
quit;

 

View solution in original post


All Replies
Grand Advisor
Posts: 10,210

Re: missing right parenthesis in PROC SQL

Do you have an example of code that worked without macro variables?

Since you are connecting to another database you have some potential conversion issues.

 

Normally to use a date literal in SAS the format would be ddMONyy or ddMONYY. So attempting to pass "01012014"d would normally fail.

 

Also, I expect you to get a number of errors from the creation of OEND that would have to be addressed first.

Occasional Contributor
Posts: 19

Re: missing right parenthesis in PROC SQL

The following code  executed perfectly in Oracle SQL developer.  I had to import this into SAS and i could not do it probably because of 

"EDW_CMC_MEPE_PRCS_ELIG.MEPE_EFF_DT_SHRT BETWEEN TO_DATE ('01/04/01', 'DD/MM/YY') and last_day (ADD_MONTHS(sysdate, -1))"

 

 

SELECT
EDW_CMC_SBSB_SUBSC.SBSB_EMPLOY_ID,
EDW_CMC_MEPE_PRCS_ELIG.MEPE_TERM_DT
FROM
EDW_CMC_SBSB_SUBSC,
EDW_CMC_MEPE_PRCS_ELIG,
EDW_CMC_PDDS_PROD_DESC,
EDW_CMC_MEME_MEMBER
WHERE
( EDW_CMC_MEPE_PRCS_ELIG.PDPD_ID=EDW_CMC_PDDS_PROD_DESC.PDPD_ID(+) )
AND ( EDW_CMC_MEPE_PRCS_ELIG.MEME_CK=EDW_CMC_MEME_MEMBER.MEME_CK(+) )
AND ( EDW_CMC_MEME_MEMBER.SBSB_CK=EDW_CMC_SBSB_SUBSC.SBSB_CK(+) )
AND
(RTRIM(EDW_CMC_PDDS_PROD_DESC.PHP_PRODUCT_TYPE_DESC) IN ( 'Centennial Care' )
AND
EDW_CMC_MEPE_PRCS_ELIG.MEPE_EFF_DT_SHRT BETWEEN TO_DATE ('01/04/01', 'DD/MM/YY') and last_day (ADD_MONTHS(sysdate, -1))
AND
EDW_CMC_MEPE_PRCS_ELIG.MEPE_ELIG_IND IN ( 'Y' )
)

Esteemed Advisor
Posts: 6,646

Re: missing right parenthesis in PROC SQL

TO_DATE ('01/04/01', 'DD/MM/YY')

is

'01apr2001'd

in SAS

 

last_day (ADD_MONTHS(sysdate, -1))

probably translates to

intnx('month',date(),-1,'end')

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎04-06-2016 01:43 PM
Respected Advisor
Posts: 3,063

Re: missing right parenthesis in PROC SQL

[ Edited ]

 This should get you closer to a solution. I'm not sure if double quotes will work in your SQL. Try double quotes with a hardcoded valid date that works with single quotes. If it fails then only single quotes are allowed. You can wrap a SAS macro variable in single quotes like this: %str(')&ostart%str(') and it will resolve OK.

 

%let ostart= 01012014;
%let oend = %sysfunc(intnx(month, today(), -1, end), mmddyy10.);
proc sql;
connect to odbc as ods
(datasrc='*********' user=------ password=------);
create table elig_facets as
select *
from connection to ods
(select edw_cmc_sbsb_subsc.sbsb_employ_id,
edw_cmc_mepe_prcs_elig.mepe_term_dt
from edw_cmc_sbsb_subsc,
edw_cmc_mepe_prcs_elig,
edw_cmc_pdds_prod_desc,
edw_cmc_meme_member
where edw_cmc_mepe_prcs_elig.pdpd_id=edw_cmc_pdds_prod_desc.pdpd_id
and edw_cmc_mepe_prcs_elig.meme_ck=edw_cmc_meme_member.meme_ck
and edw_cmc_meme_member.sbsb_ck=edw_cmc_sbsb_subsc.sbsb_ck
and rtrim(edw_cmc_pdds_prod_desc.php_product_type_desc) in ('centennial care')
and edw_cmc_mepe_prcs_elig.mepe_eff_dt_shrt between to_date ("&ostart", 'ddmmyyyy') and to_date ("&oend", 'ddmmyyyy')
and edw_cmc_mepe_prcs_elig.mepe_elig_ind in ('y')
)
;
disconnect from ods;
quit;

 

Esteemed Advisor
Posts: 5,198

Re: missing right parenthesis in PROC SQL

Given the code in the OP, I just wish to lift a syntactic problem (fixed in @SASKiwi's code), which is you must use %sysfunc() when calling SAS function in a macro variable assignment.

Data never sleeps
Occasional Contributor
Posts: 19

Re: missing right parenthesis in PROC SQL

Thank you so much for the suggestions people! Smiley Happy

Since my query was working fine in Oracle, i just used a pass through and imported the result table to SAS.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 582 views
  • 0 likes
  • 5 in conversation