topic Re: Proc format issues: invalue statement ignores length specified with informats in SAS Procedures

Proc format issues: invalue statement ignores length specified with informats

Owen — Tue, 04 Jun 2013 13:55:25 GMT

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 ================

options fmtsearch=(work);

proc format lib=work;
invalue sas_datetime_frmt
    '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-23.59.59.999999' = ['ymddttm19.']
    other = .;
run;

data work.format_in;
attrib db2_datetime length=$26;
input db2_datetime 1-26;
datalines;
1500-05-16-12.11.11.111111
1600-05-16-12.22.22.222222
1950-05-16-12.33.33.333333
9999-05-16-12.44.44.444444

;
run;

data work.format_out;
set work.format_in;
attrib sas_datetime
      length =8.
      format = datetime26.6;
    sas_datetime = input(db2_datetime, sas_datetime_frmt.);
run;

=========================================================

Results:
db2_datetime sas_datetime
1500-05-16-12.11.11.111111 01JAN1582:00:00:00.000000
1600-05-16-12.22.22.222222 16MAY1600:12:22:22.222221
1950-05-16-12.33.33.333333 16MAY1950:12:33:33.333333
9999-05-16-12.44.44.444444 16MAY9999:12:44:44.444458
.

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:

data _null_;

db2_datetime = '1600-05-16-12.22.22.222222';

sas_datetime = input(db2_datetime, ymddttm19.);

format sas_datetime datetime26.6;

put sas_datetime=;

run;

Results log:

sas_datetime=16MAY1600:12:22:22.000000

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.

Re: Proc format issues: invalue statement ignores length specified with informats

Peter_C — Tue, 04 Jun 2013 22:21:55 GMT

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:

data work.format_in;

attrib db2_datetime length=$26

db2_sas format= datetime22.3;

infile datalines truncover ;

input db2_datetime $char26. @1 db2_sas :sas_datetime_frmt. ;

list;datalines;

1500-05-16-12.11.11.111111

1600-05-16-12.22.22.222222

1950-05-16-12.33.33.333333

9999-05-16-12.44.44.444444

;

run;

and still the 1600 and 9999 timestamps include their decimal fractions

Re: Proc format issues: invalue statement ignores length specified with informats

agoldma — Sun, 29 Jun 2014 03:05:36 GMT

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 $) ;

return(input(substr(x,1,19),ymddttm19.));

endsub;

function in26(x $) ;

return(input(x,ymddttm26.));

endsub;

run;

proc format lib=work;

invalue sas_datetime_frmt

'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-23.59.59.999999' = [in19()]

other = .;

run;

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 _.

Re: Proc format issues: invalue statement ignores length specified with informats

jakarman — Sun, 29 Jun 2014 10:58:14 GMT

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.

Re: Proc format issues: invalue statement ignores length specified with informats

agoldma — Sun, 29 Jun 2014 17:45:53 GMT

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.

Re: Proc format issues: invalue statement ignores length specified with informats

jakarman — Mon, 30 Jun 2014 07:39:02 GMT

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.

Re: Proc format issues: invalue statement ignores length specified with informats

agoldma — Mon, 30 Jun 2014 12:38:42 GMT

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.

Re: Proc format issues: invalue statement ignores length specified with informats

jakarman — Mon, 30 Jun 2014 14:06:03 GMT

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?