topic Error in calling INTNX function from %sysfunc in SAS Programming

Error in calling INTNX function from %sysfunc

Kalai2008 — Mon, 03 Apr 2017 17:03:24 GMT

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.

Re: Error in calling INTNX function from %sysfunc

Astounding — Mon, 03 Apr 2017 17:16:07 GMT

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.

Re: Error in calling INTNX function from %sysfunc

Kalai2008 — Mon, 03 Apr 2017 17:53:32 GMT

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.

Re: Error in calling INTNX function from %sysfunc

Tom — Mon, 03 Apr 2017 18:05:48 GMT

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'

Re: Error in calling INTNX function from %sysfunc

Kalai2008 — Mon, 03 Apr 2017 18:12:08 GMT

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