09-08-2013 09:28 PM
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?"
09-08-2013 11:28 PM
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...
09-08-2013 11:15 PM
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
select *,intnx('day',date,12) as incdate format=date9. from have;
09-08-2013 11:36 PM
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.
09-09-2013 12:09 AM
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.
09-09-2013 01:30 AM
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.