missing right parenthesis in PROC SQL

[ashwini2]

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

[ballardw]

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.

[ashwini2]

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' )
)

[Kurt_Bremser]

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')

[SASKiwi · Accepted solution]

 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;

[LinusH]

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.

[ashwini2]

Thank you so much for the suggestions people! 🙂

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