Solved

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

Posted on 2011-09-19
9
562 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
  • 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
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 …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now