SAS Proc Append is sending back incorrect dates to MS Access DB

I am connecting to a MS Access .mdb, via an ODBC Connection from SAS, as seen below.  The ODBC Connection works perfectly.

Libname CPRIT_DB ODBC DSN=&DBName.;

The problem I have is that when I try to use a proc append to write my SAS output back to an exisiting file within the DB defined in the Libname statement, it is passing back a datetime value based on 1-1-1960 (the SAS "Day 1")

Here is the code for the data set I'm trying to output:

Data AllAwards;
   set CPRIT_DB.qry_CPRIT_ALL_Formatted;

     Project_Start_Date = Datepart(Project_Start_Date);
     Project_End_Date = Datepart(Project_End_Date);
       Budget_Start_Date = Datepart(Budget_Start_Date);
       Budget_End_Date = Datepart(Budget_End_Date);

Format Project_Start_Date Project_End_Date Budget_Start_Date Budget_End_Date MMDDYY10.;
run;


I need only the datepart to pass.  

Through a couple more data steps and a merge statement, the dataset above becomes work.Awards_ToBeAdded

I then kick off the process to append the new records back to the file in the DB defined in the libname statement:

Proc Append base=CPRIT_DB.tbl_AwardDetail data=work.Awards_ToBeAdded;
run;

************HERE IS THE ERROR MESSAGE I GET***********************

NOTE: Appending WORK.AWARDS_TOBEADDED to CPRIT_DB.tbl_AwardDetail.
WARNING: Variable Project_Start_Date has format DATETIME20. on the BASE data set and format
         MMDDYY10. on the DATA data set. DATETIME20. used.
WARNING: Variable Project_End_Date has format DATETIME20. on the BASE data set and format
         MMDDYY10. on the DATA data set. DATETIME20. used.
WARNING: Variable Budget_Start_Date has format DATETIME20. on the BASE data set and format
         MMDDYY10. on the DATA data set. DATETIME20. used.
WARNING: Variable Budget_End_Date has format DATETIME20. on the BASE data set and format
         MMDDYY10. on the DATA data set. DATETIME20. used.
UTSWPedsBudget ManagerAsked:
Who is Participating?
 
UTSWPedsConnect With a Mentor Budget ManagerAuthor Commented:
Proc Append base=CPRIT_DB.tbl_AwardDetail
                   (SASDATEFMT=(Project_Start_Date='MMDDYY10.' Project_End_Date='MMDDYY10.'
                                Budget_Start_Date='MMDDYY10.'  Budget_End_Date='MMDDYY10.'))
            data=work.Awards_ToBeAdded;

The SASDATEFMT function makes it work, perhaps magically, I'm not sure how.
0
 
Nick67Commented:
Ok,
So is what you are saying that SAS sends back a number instead of a date?
The DateSerial in MS Access is based on 30-Dec-1899 as being Day 0
1-1-1960 is day 21916  in MS Access

If it's a number coming back from SAS, you could do a DateValue(CDate(21916 + WhateverSASSends))
0
 
UTSWPedsBudget ManagerAuthor Commented:
@Nick67,

Thank you for your reply.  You are correct in that SAS is actually populating the table in Access with a bad data, but I actually need the output to be sent back correctly into the table itself.  I'm not physically opening the Access DB in any way other than through ODBC.

Thanks,
Michael
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Nick67Commented:
I am not following quite 100%
The data comes out of SAS and is bad.
It must also return to SAS?
Does Access do something in the meantime to the bad values that when they pass back in they become bad in SAS, too?

I am missing a few links in the chain of events
0
 
UTSWPedsBudget ManagerAuthor Commented:
I'm connecting to an Access DB (via ODBC, as seen in the LIbname statement).  

WIthin SAS, I take the data and do a series of steps of processing on it.  

At this point in time, after the processing in SAS is complete, I need to send that data back to MS Access (via the Proc Append).

Once the data is back in SAS, within this job, it will never "return" to SAS again.  

***The dates from go into SAS (from Access fine).  When I run the proc append, the dates it attempts to write back to the Access database are incorrect***  
0
 
UTSWPedsBudget ManagerAuthor Commented:
When I did a test on the values in work.AWARDS_TOBEADDED

Data Test;
 set Work.AWARDS_TOBEADDED;
   X = Project_End_Date - Project_Start_Date;
run;

This returns the values expected.

The problem is within the append process.
0
 
Nick67Commented:
If, on the SAS end, you can add 21916 days to the dates SAS sends back, they would likely be correct.
21916 is the number of days between 1-1-1960 and 30-Dec-1899, which is Access's day 0.

You are running the code form SAS, correct?
So that isn't syntax I know
0
 
Nick67Commented:
<This returns the values expected.>

Careful though
19-Sep-11 minus 17-Sep-11 equals 2
11-Mar-1935 minus 9-Mar-1935 also equals 2
0
 
UTSWPedsBudget ManagerAuthor Commented:
I accept this answer because it was the only one that solved the problem.  I gave my own solution a "B" because I was not able to explain the logic behind it better.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.