Solved

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

Posted on 2011-09-19
9
602 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:UTSWPeds
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 36562287
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
 

Author Comment

by:UTSWPeds
ID: 36562334
@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
 
LVL 26

Expert Comment

by:Nick67
ID: 36562432
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
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 

Author Comment

by:UTSWPeds
ID: 36562510
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
 

Author Comment

by:UTSWPeds
ID: 36562677
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
 
LVL 26

Expert Comment

by:Nick67
ID: 36562683
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
 
LVL 26

Expert Comment

by:Nick67
ID: 36562703
<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
 

Accepted Solution

by:
UTSWPeds earned 0 total points
ID: 36818465
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
 

Author Closing Comment

by:UTSWPeds
ID: 36908491
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question