topic Re: SQL Server Connectivity in SAS Programming

SQL Server Connectivity

sas_td2016 — Sat, 03 Jun 2017 19:28:46 GMT

Hello Friends

I have an issue with date format. Can you please help me with this query?

proc sql;
insert into sql.mortage
(loan_number, due_date, investor_numer, bankruptcy_flag, ltv)
values
(1000001,'01jan2016'd,200001,'N',0.8)
;
quit;

Outpout on Sql Server:

Re: SQL Server Connectivity

Reeza — Sat, 03 Jun 2017 20:13:05 GMT

You're trying to insert a date into a dateTIME field.

I suggest using DHMS to convert to a datetime and insert this instead.

Does this work for you?

proc sql;
insert into sql.mortage
(loan_number, due_date, investor_numer, bankruptcy_flag, ltv)
values
(1000001, dhms('01jan2016'd, 0, 0, 0) ,200001,'N',0.8)
;
quit;

Re: SQL Server Connectivity

sas_td2016 — Sat, 03 Jun 2017 22:50:07 GMT

Thanks for a reply. But I still I see the different date than what I entered. Instead of 01Jan2016, it shows 1960-01-01.

Re: SQL Server Connectivity

Reeza — Sat, 03 Jun 2017 23:41:53 GMT

Weird.

What happens if you use a date time literal instead of date.

"27JAN2016:12:35:00"dt

Please include your exact code, log and incorrect value as shown in your initial post.

Re: SQL Server Connectivity

sas_td2016 — Sun, 04 Jun 2017 00:01:38 GMT

Here is the actual code

proc sql;
insert into sql.mortage
(loan_number, due_date, investor_numer, bankruptcy_flag, ltv)
values
(1000001, '01jan2016'd, 200001,'N',0.8)
;
quit;

is there any formatting required for date variable?

output in sql server:

How do I get the same date as 01jan2016 in sql server?

Thanks

Re: SQL Server Connectivity

kiranv_ — Sun, 04 Jun 2017 01:05:04 GMT

did you try '2016-01-01'

Re: SQL Server Connectivity

sas_td2016 — Sun, 04 Jun 2017 03:55:59 GMT

yes, I tried. But SAS doesn't take this format.

Re: SQL Server Connectivity

kiranv_ — Sun, 04 Jun 2017 04:49:14 GMT

ok. I have worked extensively with Teradata and SAS for many years. But I have started working recently sql server. Whenever I have to do Insert I use explicit pass through a connect statement. That is always easy way to go

just tried insert into sql server '2016-01-01' and also '2016-01-01 00:00:00.000', both of them worked. Please try to connect statment if possible as shown below.

proc sql;

connect to odbc(datasrc=xxx authdomain =yyyy)

execute(insert into sqlseverschema.table values(something , '2016-01-01' )by odbc

Re: SQL Server Connectivity

Reeza — Sun, 04 Jun 2017 05:04:30 GMT

Well, that isn't the code I asked you to try. If you try the same code I'm not really surprised you're going to get the same output. If you search on here, 99% of the time, this is the correct solution. It's possible you have something else going on, but without the code/log/output I may as well tell you to walk to the server and ask what's wrong.

Re: SQL Server Connectivity

sas_td2016 — Sat, 10 Jun 2017 17:17:35 GMT

Hello Friends

I still don't understand what Reeza mentioning here. Can you please guide me on this?

My output should be same on the sql server as my sql query on the SAS.

Your help is highly appreciated.

Re: SQL Server Connectivity

kiranv_ — Sat, 10 Jun 2017 18:13:44 GMT

@sas_td2016 This is an assumption, which may not be true sometimes. A similar query run on SQL Server and SAS may have different results. One reson I can think of is case sensitive. SQL server is case insensitive, whereas SAS is case sensitive.

select * from database.dbo.mytablename

where name = 'hello'

LIBNAME SQLS ODBC DSN=’sql server’ user=sasjlb pw=pwd;

select * from SQLS.mytablename

where name ='hello'

Show us what is the code you run. what are the values you got or what is the error, someone can definetly help you.

Re: SQL Server Connectivity

sas_td2016 — Sat, 10 Jun 2017 18:29:31 GMT

Thanks Kiran for your reply.

Here is the actual code

proc sql;
insert into sql.mortage
(loan_number, due_date, investor_numer, bankruptcy_flag, ltv)
values
(1000001, '01jan2016'd, 200001,'N',0.8)
;
quit;

is there any formatting required for date variable?

output in sql server:

How do I get the same date as 01jan2016 in sql server?

In SQL Server the date shows as '1960-01-01 05:40:54:000

Thanks again.

Re: SQL Server Connectivity

kiranv_ — Sat, 10 Jun 2017 18:46:11 GMT

As @Reeza said earlier this is a datetime column, not a date column. Did you try using datetime value which @Reeza has provided. please let me know if that has nor worked.

Re: SQL Server Connectivity

sas_td2016 — Sat, 10 Jun 2017 21:38:55 GMT

yes, I tried that too. It didn't work.

Re: SQL Server Connectivity

Reeza — Sat, 10 Jun 2017 21:46:54 GMT

Show it. All you've shown so far is a DATE var which we all know doesn't work.

Re: SQL Server Connectivity

kiranv_ — Sun, 11 Jun 2017 00:23:25 GMT

check the below link someone with problem

1. https://communities.sas.com/t5/Base-SAS-Programming/Loading-sas-date-into-sql-server/td-p/235058.

please do the following if the above link does not work

1. As I mentioned and shown earlier, inserting into table using connect statement is easier bet.

2. Try using SASDATEFMT shown in the link, we used to use this a lot with date and datetime and I am sure if you following one of this example it will work.

http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#p13xka550rm442n1gdvi1swxgr1h.htm

3. Create a SAS table with format date9. with data yoou have and then insert.

Re: SQL Server Connectivity

Patrick — Sun, 11 Jun 2017 04:34:04 GMT

@sas_td2016

As other mentioned earlier you SQL Server column appears to be of data type DATETIME and not DATE.

I would have expected SAS to convert your SAS Date value to a SQL DateTime value when loading into the SQL table but it appears that's not happening.

Without you providing us with more information we can't say much more about it.

As other's already suggested try and pass in a SAS DateTime value instead of a Date value as done in code below:

proc sql;
  insert into sql.mortage
    (loan_number, due_date, investor_numer, bankruptcy_flag, ltv)
  values
    (1000001, '01jan2016 00:00:00'dt, 200001,'N',0.8)
  ;
quit;

If things still don't work then please add the following options statements before your SQL and then post the log:

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

Also: If you run a Proc Contents for table SQL.MORTGAGE what's the format on column DUE_DATE?

SAS uses this format to determine how to covert SAS Date and DateTime values to the target database representation when loading. I've made the experience that this doesn't work for all formats but that you can rely on formats DATE9. and DATETIME20.

If data conversion doesn't work as expected then another option is to explicitely "tell" SAS using option DBSASTYPE

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001371572.htm