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