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

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)

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

 

View solution in original post

9 REPLIES 9
tomrvincent
Rhodochrosite | Level 12

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
Obsidian | Level 7
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
PROC Star

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
Obsidian | Level 7

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

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
Obsidian | Level 7

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
Obsidian | Level 7

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
Rhodochrosite | Level 12

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
Super User Tom
Super User

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

 

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 9 replies
  • 1465 views
  • 2 likes
  • 4 in conversation