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
Try intnx function, but if its truly pass-through it should be your DBMS date function.
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...
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
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.
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.
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.