Help using Base SAS procedures

What do you guys use in place of DateADD in a Proc SQL?

Reply
Contributor
Posts: 20

What do you guys use in place of DateADD in a Proc SQL?

Hey Guys,

I've got a heap of old reports that were written in SQL which I am automating using Enterprise Guide.

I'm using Proc SQL/an explicit pass through to get the results into EG for me to do what I need to do with them based on the old code.

I'm getting an error with any SQL code which contains the DateAdd function in it. After some googling- I've found that SAS has no love for DateAdd and I now need to find a work around.

So I'm putting it to you, the wonderful SAS community and asking:

"What on earth do you guys use, when you can't use DateAdd?"

Thanks!

Bree

Super User
Posts: 19,877

Re: What do you guys use in place of DateADD in a Proc SQL?

Posted in reply to breelloyd

Try intnx function, but if its truly pass-through it should be your DBMS date function.

Contributor
Posts: 20

Re: What do you guys use in place of DateADD in a Proc SQL?

So here is where my understanding of what is happening must be off.

How can I have SAS code in a chunk of SQL I'm explicitly passing to our SQL Server?

the SQL server wont understand SAS.

Like I said,

I'm using a Proc SQL function.

I'm connecting to the ODBC

I'm creating a table within SAS based on the SQL code that I am explicitly passing through to the Server.

I'm not using my SAS library to run this... So I don't understand how using intrx could work, as the SQL Server wont understand it.

I musnt understand explicit pass through... It's confusing because all the other functions and SQL code I've passed through has worked. It just always fails on blocks of code with Date Add...

Trusted Advisor
Posts: 1,137

Re: What do you guys use in place of DateADD in a Proc SQL?

Posted in reply to breelloyd

As rightly suggested by reeza, it is better use the intnx function incase you wish to increase the date to a particular period. dateadd function is not loaded in the proc sql.

try the below code, where a particulat date is increased by 12 days

proc sql;

    select *,intnx('day',date,12) as incdate format=date9. from have;

quit;

Thanks,

Jagadish

Thanks,
Jag
Contributor
Posts: 20

Re: What do you guys use in place of DateADD in a Proc SQL?

Posted in reply to breelloyd

I tried using INTNX and came back with the following error:

ERROR: CLI describe error: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]'INTNX' is not a recognized built-in function

       name. : [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Statement(s) could not be prepared.

Super User
Posts: 3,260

Re: What do you guys use in place of DateADD in a Proc SQL?

Posted in reply to breelloyd

You are correct - INTNX will only work if you are querying SAS data.

If you are using Pass Thru SQL it must be compatible with the database you are running it on. It would help if you could post an example of the Pass Thru SQL that is giving you problems.   

Contributor
Posts: 20

Re: What do you guys use in place of DateADD in a Proc SQL?

Hey Guys,

Just for completeness

So I've been mucking around with this all day. I decided to go back to the drawing board and just go through all the code line by line.

Forget what I'd read on the net and take another look.

The issue was in the code where a date had been converted to Char and was being used with a SAS date.

This worked in SQL Server because dates are treated like char in the query.

So thank you so much for your assistance. I appreciate you all taking the time.

I'd read something on the net that said DateAdd doesnt work in SAS- and that's probably true- but it's fine in an explicit pass through.

problem solved!

Ask a Question
Discussion stats
  • 6 replies
  • 9102 views
  • 3 likes
  • 4 in conversation