BookmarkSubscribeRSS Feed
sas_td2016
Obsidian | Level 7

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

16 REPLIES 16
Reeza
Super User

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;
sas_td2016
Obsidian | Level 7

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

Reeza
Super User

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. 

sas_td2016
Obsidian | Level 7

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

kiranv_
Rhodochrosite | Level 12

did you try '2016-01-01'

sas_td2016
Obsidian | Level 7

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

kiranv_
Rhodochrosite | Level 12

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

             

Reeza
Super User

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. 

sas_td2016
Obsidian | Level 7

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.

kiranv_
Rhodochrosite | Level 12

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

 

sas_td2016
Obsidian | Level 7

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.

kiranv_
Rhodochrosite | Level 12

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.

sas_td2016
Obsidian | Level 7

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

kiranv_
Rhodochrosite | Level 12

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.

 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 16 replies
  • 1474 views
  • 1 like
  • 4 in conversation