06-04-2013 09:55 AM
I want to translate our db2 timestamps into a SAS datetime using informats. We now use case statements but I want to try using informats to see if I can increase our performance. We use case statements because of the limitations SAS has dealing with dates below the year 1582 and datetimes below the year 1926 or above the year 2095.
The problem is that proc format doenst look at the length I specify with the informats (see code below).
============== SAS program to test with ================
proc format lib=work;
'0000-00-00-00.00.00.000000' -< '1582-01-01-00.00.00.000000' = "&tvar_sasmindat"dt
'1582-01-01-00.00.00.000000' -< '1926-01-01-00.00.00.000000' = ['ymddttm19.']
'1926-01-01-00.00.00.000000' -< '2095-01-01-00.00.00.000000' = ['ymddttm26.']
'2095-01-01-00.00.00.000000' - '9999-12-31-18.104.22.1689999' = ['ymddttm19.']
other = .;
attrib db2_datetime length=$26;
input db2_datetime 1-26;
format = datetime26.6;
sas_datetime = input(db2_datetime, sas_datetime_frmt.);
The second and fourth date should end with .000000, but that didnt happen. I only get the .000000 when I specify a length of 19 when calling the custom
informat in the input statement and all the informats inside the custom informat are specified as ymddttm19.. It's strange because the code below does work:
db2_datetime = '1600-05-16-22.214.171.124222';
sas_datetime = input(db2_datetime, ymddttm19.);
format sas_datetime datetime26.6;
Questions I have are:
- Why doesnt proc format use the length I specify in the custom informat?
- Is there a way I can make this work?
I did try to use custom functions using proc fcmp, but the performace seems to be not as good as custom formats and case statements.
Im running these tests in a SAS 9.3 environment.
06-04-2013 06:21 PM
looks like the lengths that apply are the "external" lengths of the "outer" informat(sas_datetime_frmt.) and not the lengths defined in the embedded informats (ymddttm19.. or ymddttm26.).
I used your sample data like:
attrib db2_datetime length=$26
db2_sas format= datetime22.3;
infile datalines truncover ;
input db2_datetime $char26. @1 db2_sas :sas_datetime_frmt. ;
and still the 1600 and 9999 timestamps include their decimal fractions
06-28-2014 11:05 PM
I don't know the definition of "&tvar_sasmindat"dt, so I used the constant "01jan1582 00:00:00"dt
(it looks like this is your own way of handling date-time constants prior to 1582)
Main idea: if you want to use an [existing-informat] in your value-range-set(s), you have to use the (DEFAULT= ) option. This is documented in SAS Help: FORMAT Procedure > INVALUE Statement (near the bottom)
Only one (DEFAULT= ) option is allowed per informat-name (per invalue statement), so if you want multiple lengths, you need either 2 separate informats or use the Function Complier: PROC FCMP (similar to a macro).
Example #12 in SAS Help for Proc Format "Creating a Function to Use as a Format" is a very good example for combining a user-defined format with a user-defined function.
proc fcmp outlib=work.functions.smd;
function in19(x $) ;
function in26(x $) ;
proc format lib=work;
'0000-00-00-00.00.00.000000' -< '1582-01-01-00.00.00.000000' = '01jan1582 00.00.00'dt
'1582-01-01-00.00.00.000000' -< '1926-01-01-00.00.00.000000' = [in19()]
'1926-01-01-00.00.00.000000' -< '2095-01-01-00.00.00.000000' = [in26()]
'2095-01-01-00.00.00.000000' - '9999-12-31-126.96.36.1999999' = [in19()]
other = .;
The next 2 data steps (work.format_in and work.format_out) are the same as yours.
One possible idea for date-time values prior to 1/1/1582 is a special missing value such as A. or _.
06-29-2014 06:58 AM
I do not understand the original question.
Datetime stamps should be solved by SAS it is part of the interface http://support.sas.com/kb/50/275.html .
The year 1582 is no SAS issue the calendar has changed that moment by the pope gregorian. This was not followed in the orthodox eastern world or protestants. The oktober revolution is remembered in November.
06-29-2014 01:45 PM
Jaap, please run through Owen's original code and watch the decimals.
From what I understand, Owen would like to skip the decimals (show all zeroes for the decimals) instead of showing incorrect decimals.
That's why he would like to read pre-1926 and post-2095 dates with low precision(ymddttm19.) and contemporary dates with high precision(ymddttm26.)
Even though SAS recognizes date-time stamps between 1582 and 1926, it recognizes them only to whole seconds, not to one-millionth of a second.
On the one hand it would be great if SAS would allow 6-decimal precision for all date-time values, but on the other hand, do we really need to know that the "October Revolution" started exactly at '07nov1917 21:45:00.123456'dt? I think that '07nov1917 21:45:00'dt is close enough. Dates prior to 1582 could be off by 10 days, so I don't want to go there.
06-30-2014 03:39 AM
Agoldma, reading Owens arguments one was the gregorian argument. Agree with you that this should not be really interesting knowing the background.
The other is the number of decimals on seconds. The SAS note is telling something did change at the dbms site and SAS knows that. Fixing the issue by SAS for all involved users is making life more easy than trying to fix that by yourself. That is my argument.
06-30-2014 08:38 AM
Jaap, I'm all in favor of improving SAS, but I'm not sure that SAS is doing anything wrong in this case. My best guess is that 8 bytes is not enough precision for 6 decimals in the date-time stamp, and 8 bytes is the largest size of a numeric variable. I would have to do a lot more testing before submitting this to SAS Tech Support.
06-30-2014 10:06 AM
Agoldma, the resolution of 8 byte floating point with the offset point of zero 1 Jan 1960 is well documented. The maximum is 15 digits, I would advice not to trust more than 12. This is standard IEE that intel has hard cooked in processors.
SAS is claiming to have solved the interfaces to Datetime stamps as part of their system. Why should TS support of SAS having problems to support their claims?