BookmarkSubscribeRSS Feed
breelloyd
Fluorite | Level 6

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

6 REPLIES 6
Reeza
Super User

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

breelloyd
Fluorite | Level 6

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...

Jagadishkatam
Amethyst | Level 16

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
breelloyd
Fluorite | Level 6

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.

SASKiwi
PROC Star

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.   

breelloyd
Fluorite | Level 6

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!

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!

What is Bayesian Analysis?

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.

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
  • 6 replies
  • 30023 views
  • 4 likes
  • 4 in conversation