Error in calling INTNX function from %sysfunc

[Kalai2008]

I am new to sas programs...can someone help me solving this issue

%let drop='31-MAR-2016';

%let drop1=%sysfunc(intnx(month,&drop.d,6,s),date9.);

I am trying to get the date 6 months prior to this date..ie..31-OCT-2015..

Drop1 is not working..Throwing some oracle error..I am using Proc Sql..oracle connection.

ORACLE prepare error: ORA-00907: missing right parenthesis.

Drop works fine.

Thanks for the help.

[Astounding]

Just from what  you have shown us, you need to realize that you specified DATE9 rather than DATE11.  That means you will get 31OCT2015 rather than 31-OCT-2015 as the result.

Most likely, the error comes from your Oracle selection.  For example, you might be using this representation:

'&drop1.'d

Macro variable references within single quotes do not resolve.  If your version of Oracle SQL permits double quotes, you could use them:

"&drop1."d

Alternatively, you could switch to a more complex expression that uses single quotes:

%unquote(%str(%'&drop1.%'d))

Of course, without seeing the actual code you are using later, it could be something else entirely as well.

[Kalai2008]

Thank you for the quick reply.
The actual program code is very simple.
proc sql;
 connect to oracle(user=&userid. password=&passwd. path=**** preserve_comments);
 create table y.Cases as select * from connection to oracle
 (
WITH ids AS (

select  *

..from ....

where.....
AND trunc(c.date) =&drop1
);

 select * from ids
 );

 disconnect from oracle;
 quit;
run;

%let drop='31-MAR-2017';
%let drop1=%sysfunc(intnx(month,&drop.d,-6,e),date11.);
 

I tried changing the date9 to date11.

When I tried this  '&sdate1.'
Oracle error says "ERROR: ORACLE execute error: ORA-01858: a non-numeric character was found where a numeric was expected."

When I tried this --- &sdate1.
ERROR: ORACLE prepare error: ORA-00904: "SEP": invalid identifier

I also tried several put statements in the log
%put &sdate1.;
30-SEP-2016
  %put &sdate1.d;
30-SEP-2016d
 %put '&sdate1'.d;
'&sdate1'.d
  %put '&sdate1.d';
'&sdate1.d'
  %put '&sdate1.'d;
'&sdate1.'d
 %put "&sdate1.d";
"30-SEP-2016d"
 %put "&sdate1.";
"30-SEP-2016"
  %put "&sdate1.d";
"30-SEP-2016d"
 %put "&sdate1."d;
"30-SEP-2016"d
  %put '&sdate1.';
'&sdate1.'

Thanks for the reply.

[Tom · Accepted solution]

Does the syntax you have in your macro variable DROP work with ORACLE?

If so then make DROP1 using the same syntax.

%let drop='31-MAR-2017';
%let drop1=%unquote(%str(%')%sysfunc(intnx(month,&drop.d,-6,e),date11)%str(%'));
%put &=drop &=drop1;

Results;

DROP='31-MAR-2017' DROP1='30-SEP-2016'

[Kalai2008]

Thank you so much Tom..Awesome...it worked...!!