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:
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;
Thanks for a reply. But I still I see the different date than what I entered. Instead of 01Jan2016, it shows 1960-01-01.
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.
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
did you try '2016-01-01'
yes, I tried. But SAS doesn't take this format.
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
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.
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.
@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.
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.
yes, I tried that too. It didn't work.
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.
3. Create a SAS table with format date9. with data yoou have and then insert.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: