DATA Step, Macro, Functions and more

Error in calling INTNX function from %sysfunc

Accepted Solution Solved
Reply
Contributor
Posts: 70
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: 7,050

Re: Error in calling INTNX function from %sysfunc

[ Edited ]
Posted in reply to Kalai2008

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,509

Re: Error in calling INTNX function from %sysfunc

Posted in reply to Kalai2008

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: 70

Re: Error in calling INTNX function from %sysfunc

Posted in reply to Astounding

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: 7,050

Re: Error in calling INTNX function from %sysfunc

[ Edited ]
Posted in reply to Kalai2008

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: 70

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
  • 227 views
  • 0 likes
  • 3 in conversation