Tips for working with date, time, and datetime values in DS2

This post was kindly contributed by SAS Users - go there to comment and to read the full post.

As a programmer, you might be accustomed to how SAS® stores date, time, and datetime values as 8-byte floating-point numeric values. A SAS date is the number of days since January 1, 1960; a SAS time is the number of seconds since midnight; and a SAS datetime is the number of seconds since midnight, January 1, 1960. Are you aware that date and time values are handled differently if you are programming in DS2? Because DS2 can process databases, it has access to ANSI data types, which have greater precision. The ANSI data types that are relevant for processing dates and times include DOUBLE, TIME, DATE, and TIMESTAMP. However, these data types are not comparable to SAS date and time values, so they are not automatically converted.

Dates and times in DS2

Declaring DS2 DATE, TIME, and TIMESTAMP constants requires a specific structure. The syntax is shown here:

DATE ‘yyyy-mm-dd’
TIME ‘hh:mm:ss[.fraction]’
TIMESTAMP ‘yyyy-mm-dd hh:mm:ss[.fraction]’

Here is an example:

proc ds2;
data _null_;
method run();
dcl date ds2dt;
dcl time ds2tm;
dcl timestamp ds2dtm;
ds2dt = date '2017-01-31';
ds2tm = time '20:44:59';
ds2dtm = timestamp '2017-02-07 07:00:00.7569';
put ds2dt=;
put ds2tm=;
put ds2dtm=;
end;
enddata;
run;
quit;

Results:

ds2dt=2017-01-31
ds2tm=20:44:59
ds2dtm=2017-02-07 07:00:00.756900000

Using functions to help with conversion

When date, time, or timestamp values are read into DS2 from a database, they are processed in their native data types. Variables in a SAS data set that are formatted with a date, time, or datetime format and read into DS2 must be converted to the equivalent ANSI DATE, TIME, or TIMESTAMP data types. To successfully process ANSI values in DS2 using SAS interval functions, such as INTCK or INTNX, you must first explicitly convert them to the appropriate SAS double-precision numeric value. The following functions can assist with the conversion between ANSI and SAS:

TO_DOUBLE—converts any ANSI date, time, or timestamp value to the appropriate SAS numeric date, time, or datetime value
TO_DATE—converts an unformatted SAS date to an ANSI date
TO_TIME—converts an unformatted SAS time to an ANSI time
TO_TIMESTAMP—converts an unformatted SAS datetime to an ANSI timestamp

Example 1: Convert an unformatted SAS date, time, or datetime value in DS2

You can convert unformatted SAS date, time, or datetime values in DS2 by using the TO_DATE, TO_TIME, and TO_TIMESTAMP functions. The HAVING FORMAT option in the DECLARE (DCL) statement assigns a format to the new variables.

data dates;
sas_time=time();
sas_date=today();
sas_datetime=datetime();
run;
 
proc ds2;
data _null_;
dcl date ds2dt having format YYMMDD10.;
dcl time ds2tm having format TIME18.5;
dcl timestamp ds2dtm having format DATETIME28.5;
method run();
set dates;
ds2dt=to_date(sas_date);
ds2tm=to_time(sas_time);
ds2dtm=to_timestamp(sas_datetime);
put 'SAS: ' sas_date sas_time sas_datetime;
put 'ANSI: ' ds2dt ds2tm ds2dtm;
end;
enddata;
run;
quit;

Results:

SAS: 22326 30565.0650000572 1928996965.065
ANSI: 2021-02-15 8:29:25.06500 15FEB2021:08:29:25.06500

Example 2: Convert a formatted SAS date, time, or datetime value in DS2

If you have applied formats to the SAS variables, you must first convert the variables by using the TO_DOUBLE function. If you do not do this conversion in advance and then try to use a SAS function, such as INTNX, you see messages like the following in the log:

ERROR: Compilation error.
ERROR: Line 402: Invalid conversion for date or time type.
WARNING: Implicit conversion of time(4) type to double type. Statement 402.
WARNING: Implicit conversion of int type to double type. Statement 402.

Here is the syntax that you can use for conversion:

data dates;
sas_time=time();
sas_date=today();
sas_datetime=datetime();
format sas_time time. sas_date mmddyy10. sas_datetime datetime.;
run;
 
proc ds2;
data _null_;
dcl double SAS_Date_New having format mmddyy10.;
dcl double SAS_Time_New having format time.;
dcl double SAS_Datetime_New having format datetime.;
method run();
set work.dates;
SAS_Date_New=INTNX('week',to_double(sas_date),26);
SAS_Time_New=INTNX('hour',to_double(sas_time),3);
SAS_Datetime_New=INTNX('dtweek',to_double(sas_datetime),2);
put SAS_Date_New=;
put SAS_Time_New=;
put SAS_Datetime_New=;
end;
enddata;
run;
quit;

Results:

SAS_Date_New=08/15/2021
SAS_Time_New=11:00:00
SAS_Datetime_New=15AUG21:00:00:00

Example 3: Convert an ANSI date, time, or timestamp value to a SAS date in DS2

If you want to convert an ANSI value into a SAS value, use the TO_DOUBLE function. In the example below, the TO_DOUBLE function converts the ANSI timestamp value to a SAS datetime. After you have a SAS datetime, you can then use the SAS functions DATEPART and TIMEPART to extract the date and time values.

proc ds2;
data _null_;
method run();
dcl timestamp ds2dtm;
dcl double sas_date having format mmddyy10.;
dcl double sas_time having format time8.2;
dcl double sas_datetime having format datetime18.2;
ds2dtm = timestamp '2020-02-01 01:23:45.7569';
sas_datetime=to_double(ds2dtm);
sas_date=datepart(sas_datetime);
sas_time=timepart(sas_datetime);
put ds2dtm=;
put sas_date=;
put sas_time=;
put sas_datetime= ;
end;
enddata;
run;
quit;

Results:

ds2dtm=2020-02-01 01:23:45.756900000
sas_date=02/01/2020
sas_time= 1:23:46
sas_datetime=01FEB20:01:23:45.7

Working with both ANSI and SAS date, time, and datetime values in a DS2 program is easily managed with the TO_DOUBLE, TO_DATE, TO_TIME, and TO_DATETIME functions. These functions make the conversion between ANSI and SAS values seamless and efficient.

Thank you for reading and thanks to Mark Jordan for his expertise and help in reviewing this blog before publication.