Hi Vadim,
Ahhhh, take heart, comfort may be achieved!
If you will indulge for a moment while I try to
bring newcomers to SAS up to speed I will provide
a very possible solution for to make your code
platform and date/datetime independent (i.e. generic).
Of course if your DATEs are dates-of-birth this won't
work, unless your population is a younger one, but if
your data is DATEs larger than 1/3/1960 this could
prove helpful, see end of message below. At least it
will give you kick start on how you could develop
something that might fit your needs. And who knows,
if your data is DOB with dates prior to 1960 you may
still yet find a convoluted scheme that might do
something similar.
The DATE and DATETIME and TIME stuff is not so
ominous, portentous, prodigious, inauspicious,
needing an augur to portend, prognosticate, or
presage.
The SAS date is merely a numeric number signifying
the number of "days" since January 1st, 1960. The
SAS time is the number of "seconds" since midnight.
The SAS datetime is the number of "seconds" since
January 1st, 1960.
Thus a numeric value of 0 (zero) for TIME is
midnight.
data _null_;
x = 0;
format x timeampm.;
put _all_;
run; * x=12:00:00 AM ;
For folks new to SAS the variable x contains
the value 0. The FORMAT statement just tells
SAS to not change the value of x but just to
redisplay the cosmetics to something else at
the time it gets displayed or printed out.
The numeric value of x remains unchanged.
Also, a numeric value of 0 (zero) for DATE is
January 1st, 1960.
data _null_;
x = 0;
format x mmddyy10.;
put _all_;
run; * x=01/01/1960 ;
As well as, a numeric value of 0 (zero) for
DATETIME is midnight January 1st, 1960.
data _null_;
x = 0;
format x datetime18.;
put _all_;
run; * x=01JAN1960:00:00:00 ;
To get a better understanding by just changing
the value 0 to something larger, say 16261.
data _null_;
x = 16261;
format x mmddyy10.;
put _all_;
run; * x=07/09/2004 ;
For those new to SAS, the question may be how
does the magic work to *know* what todays date is?
(the number of days since 1/1/1960 until today)
We ourselves can do the arithmetic as well to
compute and/or convert between each of these
units of measure.
You could use arithmetic and tally up the number
of years (adjusting for leap years and leap
centuries) then months and days until you came
up with the number of days since 1/1/1960, or
we can use the DATE() function built into SAS
that will return the integer value 16261 for
today. (that's the quick way) (see, no magic)
data _null_;
x = date();
format x mmddyy10.;
y = x;
put _all_;
run; * x=07/09/2004 y=16261 days since 1/1/1960 ;
In the same vein, the number of seconds (DATETIME)
for this morning, say 7:28AM, would be 1404977321.1.
(again, I'll cheat and do it the easy way using
the built in function to fetch it) You can compute
whole numbers (i.e. whole number of seconds) but
since all numeric variables in SAS while your datastep
or proc is running are 8 byte numbers which can hold
floating point values with a mantissa. (in other words,
can store fractions of seconds as well)
data _null_;
x = datetime();
format x datetime21.;
y = x; * get value of x and leave as unformatted number ;
put _all_;
run; * x=09JUL2004:07:28:41 y=1404977321.1 seconds since 1/1/1960 ;
You can see that you can manually initiate these values
by hand instead. (no magic yet)
data _null_;
x = 1404977321.1;
format x datetime21.;
put _all_;
run; * x=09JUL2004:07:28:41 ;
or to show more fraction digits
data _null_;
x = 1404977321.1;
format x datetime22.4;
put _all_;
run; * x=09JUL2004:07:28:41.1000 ;
Fetching current TIME also has a built in function.
data _null_;
x = time();
format x timeampm.;
y = x; * get value of x and leave as unformatted number ;
put _all_;
run;* x=7:28:41 AM y=26921 seconds since midnight ;
Okay, we've seen two methods so far, 1) hardcoding a number
manually by hand into a variable, and 2) fetching the current
values via the built in function calls.
Someone may now say running the datastep with the TIME()
function is obviously going to keep returning different
values each time you run the code. Yep, same for DATE() and
DATETIME(), you may keep getting different values relative
to grabbing the now-ever-changing-current values. So a
third variation so that we can actually hardcode or get a
value for some day or time back in the past (or future) is
provided by SAS in the syntax form of specifying a string
literal with a suffix modifier letting SAS know how to do
the arithmetic to convert to the appropriate units of measure.
For example, to keep fetching the exact number of seconds
since midnight for 7:28:41 AM over and over, the string
literal (sometimes known in the SAS world of documentation
as character constants) can be specified in more familiar
representation that is more meaningful to us, but yet the
SAS program immediate converts it to the desired number
value, such as '07:28:41't would be internally converted
to 26921 for the number of seconds since midnight.
data _null_;
x = '07:28:41't; * t = convert string to TIME value ;
format x timeampm.;
y = x; * get value of x and leave as unformatted number ;
put _all_;
run; * x=7:28:41 AM y=26921 seconds since midnight ;
So indeed you can get a constant value over and over.
The same can be done, not only for TIME, for DATE and DATETIME
values as well. Remember all three of these are just a number
with three different units of measure and starting point.
data _null_;
x = '9JUL2004'd; * d = convert to DATE value ;
format x mmddyy10.;
y = x; * get value of x and leave as unformatted number ;
put _all_;
run; * x=07/09/2004 y=16261 days since 1/1/1960 ;
and for DATETIME.
data _null_;
x = '09JUL04:07:28:41.1000'dt; * d = convert to DATETIME value ;
format x datetime18.;
y = x; * get value of x and leave as unformatted number ;
put _all_;
run; * x=09JUL04:07:28:41 y=1404977321.1 seconds since 1/1/1960 ;
The forth method of handling dates and times is to convert
between them. We can use built in functions such as DATEPART()
and TIMEPART() to get just those portions.
data _null_;
x = '09JUL04:07:28:41.1000'dt; * d = convert to DATETIME value ;
format x datetime18.;
y = x; * get value of x and leave as unformatted number ;
a = datepart(x);
format a mmddyy10.;
b = a; * get value of a and leave as unformatted number ;
put _all_;
run; * x=09JUL04:07:28:41 y=1404977321.1 a=07/09/2004 b=16261 ;
the above showing how to grab the date part. below how to get the
time part.
data _null_;
x = '09JUL04:07:28:41.1000'dt; * d = convert to DATETIME value ;
format x datetime18.;
y = x; * get value of x and leave as unformatted number ;
a = timepart(x);
format a timeampm.;
b = a; * get value of a and leave as unformatted number ;
put _all_;
run; x=09JUL04:07:28:41 y=1404977321.1 a=7:28:41 AM b=26921.1 ;
However, if we remember the definition of the three units of
measure, we can easily perform the same arithmetic that these
conversion routines perform (so still no magic). for date
data _null_;
x = '09JUL04:07:28:41.1000'dt; * d = convert to DATETIME value ;
format x datetime18.;
y = x; * get value of x and leave as unformatted number ;
NumberOfSecondsInADay = ((24 * 60) * 60);
a = x / NumberOfSecondsInADay; * same as a = datepart(x);
format a mmddyy10.;
b = a; * get value of a and leave as unformatted number ;
put _all_;
run; * x=09JUL04:07:28:41 y=1404977321.1 a=07/09/2004 b=16261 ;
and for time
data _null_;
x = '09JUL04:07:28:41.1000'dt; * d = convert to DATETIME value ;
format x datetime18.;
y = x; * get value of x and leave as unformatted number ;
NumberOfSecondsInADay = ((24 * 60) * 60);
a = mod(x,NumberOfSecondsInADay); * same as a = timepart(x);
* instead of the mod() function to get the leftover seconds
within a whole day you could also manually compute the
days for the date and multiply by number of seconds in a
day and then subtract that from x to get the leftover
seconds less than one day which is the TIME part. ;
format a timeampm.;
b = a; * get value of a and leave as unformatted number ;
put _all_;
run; * x=09JUL04:07:28:41 y=1404977321.1 a=7:28:41 AM b=26921.1 ;
Now being comfortable with how date and time values work and
that you can convert, compute, adjust them arithmetically
yourself such as add a day to a DATE, since a DATE is number
of days, merely add 1. If you wanted to add an minute to TIME
then just add 60, since the TIME unit of measure is in seconds.
Adding an hour would just be adding (60*60), i.e. 60 seconds
times 60 minutes would equal and hour, and (60*60)*24 would
be equal to a day in seconds.
Now back to your dilema. A possible safe platform and DATE
and DATETIME independent scheme of logic might be that you
could determine that you would never have any DATEs in your
data that occur in the next 500 years.
data _null_;
x = '1JAN2500'd; * d = convert to DATE value ;
format x mmddyy10.;
y = x; * get value of x and leave as unformatted number ;
put _all_;
run; * x=01/01/2500 y=197232 ;
and if we check the 197232 converted into DATETIME seconds
we see that it represents
data _null_;
x = 197232;
format x datetime22.;
y = x; * get value of x and leave as unformatted number ;
put _all_;
run; * x=03JAN1960:06:47:12 y=197232 ;
So *IF* the DATEs in your data were not DOBs, or at least
DOBs greater than January 3rd of 1960, then you could use
generic logic for both DATE or DATETIME platform conversions
with something like checking the magnitude of the number
value to interpret if the incoming value is in a DATE unit
of measure or in a SECONDS unit of measure:
* convert from DATETIME units if needed ;
if mydate > 197232 then mydate = datepart(mydate);
* else leave mydate in DATE units ;
Of course if you needed Jan 2nd and 3rd of 1960, and you can
deal with only 100 years of cushion you could use a smaller
number:
data _null_;
x = '1JAN2100'd; * d = convert to DATE value ;
format x mmddyy10.;
y = x; * get value of x and leave as unformatted number ;
put _all_;
run; * x=01/01/2100 y=51135 ;
data _null_;
x = 51135;
format x datetime22.;
y = x; * get value of x and leave as unformatted number ;
put _all_;
run; * x=01JAN1960:14:12:15 y=51135 ;
and end up with
if mydate > 51135 then mydate = datepart(mydate);
(so still no magic)
Now many times DATEs are for DOB etc with values back to,
and prior to, 1960 and this suggestion is quite silly.
However, the point is that with the comfortable understanding
of how days and seconds are stored in a number, for the different
units of measure, -and- if you know your data well, you may
have the *opportunity* to utilize some arithmetic scheme to
allow you the luxury of coming up with something smooooth!
(if you have such circumstances that would allow something
tricky then you WILL have made some *magic*)
Hope this is helpful,
Mark Terjeson
Reporting, Analysis, and Procurement Section
Information Services Division
Department of Social and Health Services
State of Washington
mailto:***@dshs.wa.gov
-----Original Message-----
From: VP1695 [mailto:***@AOL.COM]
Sent: Friday, July 09, 2004 5:34 AM
To: SAS-***@LISTSERV.UGA.EDU
Subject: DATEPART function
I just wanted to warn those of you who use the DATEPART function.
As many of you know, this function extracts the date from a SAS
datetime value. However, if you use DATEPART on the date (not
datetime) variable, the value of the function will become 0 which
corresponds
to 01JAN1960
in SAS. Why would anybody want to use this function on the date? Well, I'll
give you a scenario. Let's say you extract data from Oracle using PROC SQL.
For some reason, SAS treats Oracle dates as datetime variables, and if you
want dates you may decide to use the DATEPART function in your SAS code.
Now, say, at some later point your database, data warehouse,... migrates to
another platform, say, Teradata. You are unaware that SAS reads Teradata
dates as dates and are using the same SAS code with the DATEPART functions
in it. It would be nice if SAS warned you that the argument of the DATEPART
function is invalid, but SAS doesn't give either an error message or at
least a warning and you are getting 01JAN1960 all over without even knowing
it.
Vadim Pliner