09-14-2015 06:20 PM - edited 09-14-2015 06:21 PM
Dear SAS Community -
Please bear with me as I try to explain the situation and what I'm trying to accomplish...
Preface: Currently, I am creating new tables from old ones by converting many character fields to date fields using SAS DI Studio. I'm basically just making a new column and writing the input(old_field, yymmdd8.) and changing the format to mmddyys10. The reason I am doing this is because we want to make all the fields that have date values into a date field (They are currently character fields.). So far, everything is working as far as converting them.
Situation: When I open these new tables in SAS EG and Excel (using the SAS tab) and test them with queries and date prompts, everything is working fine. However, when I open these new tables in Brio or Access, the new fields are not converting properly into the format (mmddyys10.) that I assigned them. They are showing number values like 1,974 (or something like that) where in EG/Excel, that same value would be the correct format of something like 08/21/1998.
What I Know: I was told that when passing the data through Access and Brio, it's done through an ODBC server. Through SAS EG and other SAS modules, it's through an IOM server. I think this has something to do with it. I could be wrong...
Problem: I need these new date fields I created to transfer as date fields with the format I have assigned them into Access and Brio. How do I accomplish this? Is this something done on the backend by a person who can modify settings for the server and how data is transferred? Do I need to format my dates differently in DI Studio so they can convert properly when I open the tables in Access and Brio? Or do I have to just manually code them in Access and Brio? Or a combination of some/all of these?
Any help on the issue would be much appreciated. Thanks.
09-15-2015 06:50 PM
I have not been able to test this because I do not have MS Access drivers for SAS EG installed, but try converting your date variables to datetime. Many database platforms do not distinguish between date and datetime. Dates are simply datetime values that happen to be divisible by 86400 (or whatever the precision happens to be). MS Access happens to be one of those platforms.
Multiply each of your date variables by 86400 (the number of seconds in each day), and see if that helps. I would love to know if that works since I have no way of testing this theory
DATA want; SET have; FORMAT good_datetime datetime20.; good_datetime = bad_date*86400; RUN;
As an aside, exporting large dates such as the all too common "12/31/9999" to other platforms (not sure if MS Access is one of them) may result in some unexpected problems because of the cumulative effect of leap seconds that will have occurred before 12/31/9999.
Eventually, the cumulative effect of the Gregorian Calendar's leap seconds will add up to a full day a couple or several millennia from now, and some databases (and/or drivers, service releases, etc.) seem to do the conversion inconsistently, causing 12/31/9999 to be nudged to 1/1/10000, an often unsupported value in the target database.
09-18-2015 12:04 AM
Come to find out... when typing the formats and informats for dates from SAS DI Studio, they must be all caps. It not, access and brio will not read them properly. Weird how I couldn't find this on the internet and I searched pretty hard for something like this. But, I believe the lowercase "mm" in mmddyy10. conflicts with access and brio, because access and brio thinks mm = minutes, not months. Anyways, I figured it out and that was the end of it. Thanks for the reply.
10-25-2015 05:18 AM - edited 10-25-2015 05:19 AM
Are you sure that this was the issue? It's the first time that I hear the casing for formats is relevant. MS Access won't "see" the SAS formats as it's the SAS Access engine which will convert the data.
What I've experienced in the past though: Because SAS doesn't have a data type for dates the access engine needs to identify dates based on formats applied to numeric variables. Only a few date formats are recognized as documented here - else SAS will treat the values like any other number:
If you don't want to use the few available formats for dates then using the DBTYPE data set option is another way to go