Solved

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

Posted on 2011-09-19
9
581 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

808 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