DATA Step, Macro, Functions and more

Error in calling INTNX function from %sysfunc

Accepted Solution Solved
Reply
Contributor
Posts: 53
Accepted Solution

Error in calling INTNX function from %sysfunc

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.


Accepted Solutions
Solution
‎04-03-2017 02:11 PM
Super User
Super User
Posts: 6,497

Re: Error in calling INTNX function from %sysfunc

[ Edited ]

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'

 

View solution in original post


All Replies
Super User
Posts: 5,071

Re: Error in calling INTNX function from %sysfunc

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.

Contributor
Posts: 53

Re: Error in calling INTNX function from %sysfunc

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.

Solution
‎04-03-2017 02:11 PM
Super User
Super User
Posts: 6,497

Re: Error in calling INTNX function from %sysfunc

[ Edited ]

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'

 

Contributor
Posts: 53

Re: Error in calling INTNX function from %sysfunc

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 174 views
  • 0 likes
  • 3 in conversation