SAS / SQL Passthru with CONVERT DATEADD and DATEDIFF function examples

[15GreyGoose]

Hi!

I've been asked to update some old SQL code and re-write it in SAS 9.4.  The old SQL query has SQL Convert, DateAdd, and DateDiff functions that are not supported in SAS and I've been struggling with this for most of the day.  I'm using Proc SQL / an explicit pass through to get the results into SAS 9.4 for me to do what I need to do with the data.

Specifically, here are some lines of exact code that I'm trying to manipulate.  I believe it is a combination of SAS Input functions along with whatever the SAS equivalent is for the SQL DateAdd and SQL DateDiff:

Old SQL Syntax Line #1):

Convert(Date, APP_DATE_AJUDICATED)

New SAS replacement syntax:

Old SQL Syntax Line #2: 

Convert(Date,DATEADD(MONTH, DATEDIFF(MONTH, -1,  APP_DATE_AJUDICATED )+0, -1))

New SAS replacement syntax SAS: 

Old SQL Syntax Line #3:  Convert(Date, DateAdd(D,1, APP_DATE_AJUDICATED ))

New SAS replacement syntax: 

I'm a bit over my head and haven't been able to make much progress on this this morning so I'm turning to the SAS community for assistance!

Please help!,

Andrew

[utrocketeng]

Greetings 15GreyGoose.  i pass most of my queries from SAS to the server hosting the data.  by doing this, you can use just about any compliant SQL Statement (in my experience).  the only common exception for me is the use of '--' for comment code in SQL Server.  SAS does not seem to like that here.  i suggest using block style commenting:  /* comments */


here is a basic template you can use:

PROC SQL;
   CONNECT TO ODBC as Alias
    (DATAsrc="DSN" authdomain=IfOneIsSetUp);
 
   CREATE TABLE work.thing  AS 
   SELECT *
      FROM CONNECTION TO Alias 
(
/*SQL Code*/

SELECT Convert(Date,DATEADD(MONTH, DATEDIFF(MONTH, -1,  APP_DATE_AJUDICATED )+0, -1)) FROM YourDbTableName
);
 
   DISCONNECT FROM Alias;
QUIT;

you will need to know the DSN for the remote server hosting the data.  you might be able to find the DSN by right click the Libname under libraries.  you will also need credentials to connect, we use an authodomain, but something like this could work as well:

(noprompt = "server=ServerName; UID=UserId; pwd=UserPassword;DRIVER=SQL Server; DATABASE = DatabaseNameOnServer" )