Solved

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

Posted on 2011-09-19
9
591 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

734 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