BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ashwini2
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

 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

6 REPLIES 6
ballardw
Super User

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
Fluorite | Level 6

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
Super User
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
PROC Star

 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
Tourmaline | Level 20

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
ashwini2
Fluorite | Level 6

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.

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!

How to Concatenate Values

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.

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
  • 6 replies
  • 5363 views
  • 0 likes
  • 5 in conversation