DATA Step, Macro, Functions and more

SQL Server Connectivity

Reply
Occasional Contributor
Posts: 17

SQL Server Connectivity

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:

Capture.JPG

Super User
Posts: 17,829

Re: SQL Server Connectivity

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;
Occasional Contributor
Posts: 17

Re: SQL Server Connectivity

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

Super User
Posts: 17,829

Re: SQL Server Connectivity

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. 

Occasional Contributor
Posts: 17

Re: SQL Server Connectivity

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:

Capture.JPG

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

Thanks

PROC Star
Posts: 252

Re: SQL Server Connectivity

did you try '2016-01-01'

Occasional Contributor
Posts: 17

Re: SQL Server Connectivity

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

PROC Star
Posts: 252

Re: SQL Server Connectivity

[ Edited ]

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

             

Super User
Posts: 17,829

Re: SQL Server Connectivity

[ Edited ]

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. 

Occasional Contributor
Posts: 17

Re: SQL Server Connectivity

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.

PROC Star
Posts: 252

Re: SQL Server Connectivity

[ Edited ]

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

 

Occasional Contributor
Posts: 17

Re: SQL Server Connectivity

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:

Capture.JPG

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.

PROC Star
Posts: 252

Re: SQL Server Connectivity

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.

Occasional Contributor
Posts: 17

Re: SQL Server Connectivity

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

PROC Star
Posts: 252

Re: SQL Server Connectivity

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

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

 

Ask a Question
Discussion stats
  • 16 replies
  • 217 views
  • 1 like
  • 4 in conversation