SQL Insert Into Statement to pass SAS date to MS Access DB

Question

I would like to append the data in a SAS dataset to a table in a MS Access database. In the past, I've used a SQL INSERT INTO statement to do this, but I'm having some trouble with inserting the SAS date field into the MS Access Date/Time field. Here's an idea of what I'm trying do in the SAS program:

libname input "c:\Folder";
libname db access 'C:\Folder\database.accdb';

Proc SQL;
Create table tblAccess as
Select distinct field1, field2, DOS
from input.dat1;

PROC SQL;
Insert into db.tblClaims (field1, field2, DOS)
select field1, field2, DOS
from work.tblAccess;

The problem with this is that the DOS field is showing up as "1/1/1960 5:52:XX AM" in the MS Access database, regardless of what the DOS was originally. Obviously, the issue is that the SAS date is being entered into MS Access as a datetime - the SAS date of 12/10/2017 is being stored as the numeric 21163, which is being inserted into the MS Access DB as 21,163 seconds (~5 hours, 52 minutes) from 1/1/1960. In other words, somewhere along the way, my days are being treated as seconds, throwing everything off, if that makes sense.

But while I understand the issue, I can't seem to figure out how to fix it. Alternatively, I'd appreciate suggestions for other ways to approach this task that avoids this date/time field issue. As far as I can tell, PROC EXPORT won't work because the exported SAS dataset replaces the MS Access table and can't append to it, at least from my experiments with it.

Edit: the SAS field is DATE9. and the MS Access field is Date/Time, in case that's relevant.

Answer 1

Re-stating the problem for Stack Overflow helped me see the solution. If the problem is that days are being turned into seconds, why not turn them back into days? I added the following DATA step in before the second PROC SQL statement:

Data tblAccess;
set work.tblAccess;
DOS = DOS*86400;
Format DOS Datetime9.;
run;

It appears to have resolved the issue. However, I would still appreciate better methods to perform this task.

Answer 2

SAS stores date values as the number of days and datetime values as the number of seconds. You can convert from date to datetime using the DHMS() function. DHMS stands for Days Hours Minutes Seconds, You can convert the other way using the datepart() function.

insert into db.tblClaims (field1, field2, datetime_field)
  select field1, field2, dhms(date_field,0,0,0)
  from work.tblAccess
;

Answer 3

I am going to start off by saying I don't have experience with the exact problem you've encountered, but perhaps something similiar. Also, its been years since I have worked with sas on any serious level.

That being said, you most likely want to work with the format of the date field in question. Something like this may work:

Data tblAccess;
SET work.tblAccess;
Format DOS YYMMDD10.;
run;

OR Alternatively (in the Proc SQL):

Proc SQL;
Create table tblAccess as
Select DISTINCT field1, 
    field2, 
    DOS format=YYMMDD10.
from input.dat1;

It's been too long for me to know for sure what Format you need, but a general review of other possible formats with a search engine check may yield the answer you need (assuming this doesn't work).