Hello all. This is a very general question but I still have not been able to find the solution online.
Basically whenever I am insert dates into sql, I have to convert the dates into datetimes and have the values of 00:00:00 attached.
My question is, if you have a sql table with a column of "Date" format, how do you get SAS dates to insert into this field? When I try an insert option, sas throws the error "column X is not of the correct datatype". When I try to create a table, SAS converts the dates into 1960:03:00:93 (converting the date into seconds from 1960-01-01.
Any help would be greatly appreciated!
Thanks!
Brandon
I can't help with this but you might want to describe exactly how you are connecting to the database and examples of code. Others with more experience will have more information to diagnose and help with a more concrete example of what you have attempted.
Anotherdream wrote:
Basically whenever I am insert dates into sql, I have to convert the dates into datetimes and have the values of 00:00:00 attached.
I think that's the answer there. Or do you need to know how to do that conversion?
You could write a macro that converts all date variables to datetime variables with 00:00:00.
I dont think SQL Server added a date only data type until 2008 and then developers would have to use that for date only fields. Previous datetime I believe was the only storage option (of course the view could be formated to show what every the developer wanted). Since you are having this issue I imagine that your solution, like suggested, is to add the time to the dates you have.
I have done this in the past as part of the insert sql, but you could do it in a datastep prior to the insert as well.
EJ
I understand that the data type was only added in 2008, however this seems like a fundamental short-sight of being able to transfer data between two sources. EXAMPLE: What if a sql table has a field that is specified as a "date" format. You cannot then take ANY sas data and get data into this field and have it mapped correctly as a date. If you format it as a standard Date, or character, or Datetime, sas throws the errror (Type of column 2 not recognized).
Here is some example code that creates such a table and tries to insert data into it.
SQL SERVER CREATE TABLE STATEMENT
Create table dates_test(
[KEY] NUMERIC null,
[Datefield] [date] null)
on [primary]
go
Sas code to insert into this table
data person;
infile datalines delimiter=',';
informat key best32.;
informat datevalue mmddyy10.;
format key best32.;
format datevalue mmddyy10.;
input key datevalue ;
datalines;
1,01/01/1999
2,02/27/2009
;
libname CstOrder odbc dsn='odbc_my_odbcname' schema=dbo bulkload=yes DBMAX_TEXT=32000 ;
proc sql;
insert into cstorder.DATES_TEST
select * from person;
quit;
run;
Is there no way to get the date field from SAS into a corresponding date field in an already created sql table? (basically this would imply we cannot use the date field datatype for any tables and have it communicate with sas, which again seems like a massive defect).
Thanks!
Brandon
My appoligies I think I misunderstood your issue. Is it that you have dates in SAS and are trying to use the actual date datatype in SQL server? if that ist the case I have not dealt with that as most of our SQL Server developers just use the historical datetime field.
This may be better addressed with SAS Tech Support, I would assume that the SAS date field should correctly convert to the SQL server date field.
EJ
Hello esjackso1. That is correct, you have perfectly stated my problem. I am able to get datetimes into sql server, but I cannot get an actual DATE into a field of the datatype DATE, which is a problem because quite a few of our tables are using the "DATE" format (in sql).
I will open a ticket with sas tech support, however I am curious if anyone at all has run into this problem before?
Thanks all!
Hello all. I am sorry for the inconvience, I have just discovered how to solve this problem.
It turns out, if you specify the SAS data as a charater value, in the format MMDDYY10., then insert it into the sql table (with the DATE Type) sql server actually convers the character value into a "DATE" field.. (very strange I think).
Thanks all for your time!
Brandon
Good to know ... Thanks for posting your solution!
EJ
Doesn't sound like a best practice to me...
Have you tried DBSASTYPE?
Is that for when specifying the date as a literal value? or when inserting from a SAS dataset?
Can't see any use when specifying constants, since you have the possibility to use the appropriate format when specifying the constant. So it's for column values, and it works both ways (read/write). For inserting, see example 3:
SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition
Wonder if there's any chance of reviving this as I have a question - I tried using DBSASTYPE in the following context:
data SQLlib.TasksNotRun (DBSASTYPE=(LastRunSuccess='DATE'));
set TaskList;
where LastRunSuccess lt LastExpectedRun;
run;
This uses an ODBC connection for the 'SQLlib' libname and I'm using SQL Server 2014 for my databases.
The input data column, from the table 'TaskList', is in YYMMDD10. format. However the table it creates in SQL has a datetime format for this column.
Not a disaster for what I'm using it for, but I wanted my output to look nicer...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.