Converting to prompt in oracle data import

[RAW_newbie]

I am attempting to convert a successful import operation using a "static" date range to a date range received from a prompt.  The goal is to let the date range "roll" with a current date to a following date of last month. 

I am receiving an ORACLE prepare error: ORA-00907: missing right parenthesis.

Or is there a better way to accomplish the moving date range idea?

The following correctly imports oracle data using static date range: 

proc sql;
     connect to oracle(authdomain='DefaultAuth' path='********');
     create table transactions as
     select distinct * from connection to oracle
       (    
   SELECT distinct *         
   FROM ***.tr*****
   where
   EFFECTIVEDATE BETWEEN TO_DATE('01/03/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
    AND TO_DATE('01/12/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
  );
 disconnect from oracle;
 quit;

The following causes an error: 

proc sql;
     connect to oracle(authdomain='DefaultAuth' path='********');
     create table transactions as
     select distinct * from connection to oracle
       (    
   SELECT distinct *         
   FROM ***.tr*****
   where
   EFFECTIVEDATE BETWEEN TO_DATE("&Report_DT_MIN."d) AND TO_DATE("&Report_DT_MAX."d)
  );
ERROR: ORACLE prepare error: ORA-00907: missing right parenthesis. SQL statement: SELECT DISTINCT *    FROM ***.tr***** where
EFFECTIVEDATE BETWEEN TO_DATE(07Jan2018) AND TO_DATE(29Jan2018).
 disconnect from oracle;
 quit;

SAS EG Version 7.11 HF3 (7.100.1.2805) (32-bit)

[tomrvincent]

I wrote this macro that formats a sas macro variable as an oracle date properly

%macro oracle_date(datefld);
    /* formats a date variable for use in oracle sql
    example:

    %let dt1=%sysfunc(today(), date9.);
    %oracle_date(dt1);

    b4: date parameter DATEFLD=dt1 value &=19OCT2017
    after: date parameter DATEFLD=dt1 value &='19-OCT-2017'
    */

    %put b4: date parameter &=datefld value &=&&&datefld;
    %let &&datefld = %str(%')%Sysfunc( InputN( &&&datefld , date11 ),date11 )%str(%') ;
    %put after: date parameter &=datefld value &=&&&datefld;

%mend oracle_date;

[RAW_newbie]

So the error is a result of the format of the value being returned by the prompt?

EFFECTIVEDATE BETWEEN TO_DATE("&Report_DT_MIN."d)
EFFECTIVEDATE BETWEEN TO_DATE(07Jan2018)

[SASKiwi]

There are two problems with what you are trying to do:

1) The SAS date format "ddmonyyyy"d is not compatible with Oracle

2) You have dropped the second parameter on the Oracle TO_DATE function

From memory I think Oracle would be OK with "07-Jan-2018" without using TO_DATE so try this as a test:

%let Report_DT_MIN = 07-Jan-2018;

%let Report_DT_MAX = 29-Jan-2018;

EFFECTIVEDATE BETWEEN "&Report_DT_MIN" and "&Report_DT_MAX"

[RAW_newbie]

Thank you for the simplification of EFFECTIVEDATE BETWEEN "&Report_DT_MIN" and "&Report_DT_MAX"

While this works with single quotes ( %let Report_DT_MIN = '07-Jan-2018'; ) around the date, it still doesn't allow for the use of the prompt I was hoping for.

Reformatting the values returned from the prompt 07Jan2018 to 07-Jan-2018 apparently is where I need to get to.  Alas, inexperience and ignorance on my part has prevented my success.

So how to reformat the prompt to the needed format. 

[SASKiwi · Accepted solution]

Try this then substitute in your prompt names:

%Let prompt1 = 07Jan2018;
%Let prompt2 = 29Jan2018;
%let Report_DT_MIN = %sysfunc(putn("&prompt1"d, date11.));
%let Report_DT_MAX = %sysfunc(putn("&prompt2"d, date11.));
%put &Report_DT_MIN &Report_DT_MAX;

EFFECTIVEDATE BETWEEN %str(%')&Report_DT_MIN%str(%') and %str(%')&Report_DT_MAX%str(%')

[RAW_newbie]

Alright, thank you everyone.  All the suggestions were very instructive for me.

After trying the suggestions, SASKiwi's fit the requirements and environment the best.

It allows me to use the prompt feature across multiple processes within the project as well.

[RAW_newbie]

Final product:

%let DATA_DT_MIN = %sysfunc(putn("&Data_Pull_DT_MIN"d, date11.));

%let DATA_DT_MAX = %sysfunc(putn("&Data_Pull_DT_MAX"d, date11.));

%put &DATA_DT_MIN &DATA_DT_MAX;

　

proc sql;

connect to oracle(authdomain='DefaultAuth' path='********');

create table transactions as

select * from connection to oracle

( SELECT DISTINCT *

FROM f***.T********

where

EFFECTIVEDATE BETWEEN %str(%')&DATA_DT_MIN%str(%') and %str(%')&DATA_DT_MAX%str(%')

);

disconnect from oracle;

quit;

　

[tomrvincent]

here's how I do the sql:

PROC SQL;
   CONNECT TO ORACLE AS bez(USER = &Usr. PASSWORD = "&Password_PASSWORD" PATH = &Path.);
   CREATE TABLE CLAIMS_100_RPT5_2016_2017_&Pdmonth. AS
   SELECT *
      FROM CONNECTION TO bez
        (SELECT
LOB,
CAP_FFS_INDICATOR
FROM
MHP.FACETS_CLAIMS
where     
SERVICE_FROM_DATE BETWEEN &sdb and &sde /*'1-Jun-16' AND '31-Dec-17'*/
and PAID_DATE between &pdb and &pde );

ends up looking like this in the log:

CREATE TABLE CLAIMS_100_RPT5_2016_2017_Pd0118 AS SELECT * FROM CONNECTION TO bez (SELECT LOB,  CAP_FFS_INDICATOR FROM MHP.FACETS_CLAIMS where SERVICE_FROM_DATE
BETWEEN '01-APR-2017' and '30-JUN-2017' and PAID_DATE between '01-APR-2017' and '31-JAN-2018' );

So, for you, I'd go with this:

%oracledate(Report_DT_MIN);

 %oracledate(Report_DT_MAX);

then this in your proc sql:

EFFECTIVEDATE BETWEEN &Report_DT_MIN and &Report_DT_MAX

[Tom]

One of the big problems with generating pass thru SQL code is that most database insist on using single quotes for string literals (reserving double quotes for object names).  So I find it useful to use a simple SAS macro to make it easier to add single quotes around a value.  

If you are using a recent version of SAS it is simply a call to the QUOTE() function with the optional second parameter.

%macro squote(value);
%sysfunc(quote(%superq(value),%str(%')))
%mend squote;

But if you are running an old version of SAS then you might need to use this more complicated version. https://github.com/sasutils/macros/blob/master/squote.sas

So now if you can get your date value into a format that your database recognizes as a date you can then use %SQUOTE() to enclose it in single quotes for you. 

For example this will convert your macro variable in DATE format into a date in YMD format inside of single quotes to pass to the TO_DATE() function.

EFFECTIVEDATE
  BETWEEN TO_DATE(%squote(%sysfunc(putn("&Report_DT_MIN."d,YYMMDD10)))) 
      AND TO_DATE(%squote(%sysfunc(putn("&Report_DT_MAX."d,YYMMDD10))))