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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

4 REPLIES 4
Astounding
PROC Star

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
Pyrite | Level 9

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

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
Pyrite | Level 9

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 1221 views
  • 0 likes
  • 3 in conversation