sas, 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

corresponds
I'll
SQL.
to
DATEPART
knowing

Hi, Vadim,

I can understand the pain when you must have felt when you discovered that
the migrated data are incorrect.

Transferring (or migrating) data from one system to another, however, are
inherently difficult, in my opinion, because it requires the user to be
familiar with both the systems that are involved, plus whatever the
software tool or standard that are used in the process. This also means
that as long as different systems are involved, there are no reasons to
assume that the transfer code you wrote would work the same way as it did
before with a different system.

The best kind of transfer software tools, IMHO, let you specify, for each
var, the exact data types to be used on both input and output sides; it
should also let you spell out on which side the data type conversion
should occur and in which fashion. I am quite pleased with some of the
sas/access products in this regard.

The worst kind is the ones that try to be "smart" and hide all the details
from the user or, worse, do not allow such detailed user control over the
transfer process. Alas, it seems to me that the latter, rather then the
former, is the more popular kind.

Regarding the sas's datepart function, you may benefit from reminding
yourself that sas has only two different data types: numeric and
character. There are no date types. As such, the input to the datepart
function is simply a numeric type variable. Thus it is simply impossible
for sas to "detact" or "warn" user about the possible abuses like you
mention.

Hope this helps and good luck with your data migration in the future.

Cheers,
Chang

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

i Chang,

I do remember that SAS has only two different data types: numeric and
character and there are no date types in SAS. However, I don't understand why
"it is simply impossible for sas to warn" about a potential misuse of the
DATEPART function. You are right, all date and datetime variables are numeric,
but their formats are different. Why couldn't SAS tell a date format from a
datetime one? Even if it was impossible, is it logical that the DATEPART of any
date (numeric variable formatted as a date) is 0 i.e. '01JAN1960'?

Vadim

understand why
numeric,
a
of any

Hi, Vadim,

Yes. You are correct - it is possible. Maybe we need two functions:
datePartX() that takes a numeric expression, and safeDatePart() which
takes only either a dt literal or a numeric variable with a dt-type
format.

Wait! We may probably need two more functions: safeDatePartE(), which
checks the format of the input variable during the execution time; and
safeDatePartC(), which does it in the compile time.

While we are at it, why don't we ask sas for two more? safeDatePartEN(),
which returns a numeric variable formatted with a date format which is
most compatible with the datetime format of the input variable, and
safeDatePartEC(), which returns a character string of the date value
already formatted with the associated format.

Hmmm, we may need two more because we have the C versions: safeDatePartCN
() and safeDatePartCC()

Well, how many (data step) functions we already have? Do you remember the
exact difference between vlabel() and vlabelx()? How about cat...()
family? Why can't we write our own functions using data step language?
... We may be better off just asking sas for the genuine, date, time, and
datetime types. ... Oh, wait, if we have them ... then, how many more
input...() and put...() family functions should we have? ... how the
automatic conversion (or type-promotion) rules would read like?

Happy Friday!

Cheers,

Chang

Thank you for the warning.
The migration process should have considered warehouse usage and undesired
side effects.
<harsh>
Oracle date type is a datetime value, Teradata date type is a date value.
Dont use DatePart function with date values.
A joy of speciation is that different things are indeed different and
require understanding
Also, a warning for US drivers: when in the UK, look out. They drive on the
wrong side of the road.
p.s. Mosquito repellent isn't working against bees.
Snif, our shiny new Maserati bottoms out on the same road our old Jeep
didn't.
</harsh>

--
Richard A. DeVenezia

ou can write your own safety net using the VFORMATN function to examine
the format associated with a variable. But that's not a sure thing. A
variable does not have to have a format, and in particular datetime
variables work very nicely in internal calculations regardless of whether
they have formats (which are basically for presentation). Also keep in mind
that it's a carpenterian (as in
http://www2.sas.com/proceedings/sugi23/Training/p275.pdf) "best" practice
to store variables with associations to utterly inappropriate formats, then
apply the appropriate ones on the fly (of course by means of intricate and
obscurely named macros).

On Sat, 10 Jul 2004 01:19:43 GMT, VP1695 < XXXX@XXXXX.COM > wrote:

why
numeric,
of any
you
functions

I realize this doesn't help Vadim right now, but it may be helpful to others
who access Oracle from SAS.

The datetime values returned by SAS when accessing Oracle aren't
particularly helpful in my experience ( i.e. the time portion is always zero
).

Rather than dealing with the datepart() function in SAS, I found it helpful
to convert the Oracle date value to a SAS date value in the pass-thru query
using a simple little macro.

Note that infld can be table.fieldname notation and, optionally, outfld can
be specified if a different field name is required in the output dataset.


%macro to_sasdate(infld,outfld);
%if &outfld = %str() %then %let outfld = &infld;
%if %index(&outfld,.) %then %let outfld = %scan(&outfld,2,.); /* if
table_name.column, strip table_name */

&infld - to_date('01jan1960','ddmonyyyy') as &outfld

%mend to_sasdate;

proc sql;
connect to oracle ( );
create table saslib.sasdata as
select * from connection to oracle (
select field1
,field2
,%to_sasdate(oracle_date_field)
,field3
from oracle_table )
;
disconnect from oracle;
quit;

-----Original Message-----
From: VP1695 [mailto: XXXX@XXXXX.COM ]
Sent: Friday, July 09, 2004 8:34 AM
To: XXXX@XXXXX.COM
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

------------------------------------------------------------This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.

Ce courrier ectronique est confidentiel et prot L'expiteur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) digns) est interdite. Si vous recevez ce courrier ectronique par erreur, veuillez m'en aviser immiatement, par retour de courrier ectronique ou par un autre moyen.

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

nother idea that may be useful for keeping dates 'stable' across database software, would be to convert the date into text before transferring the data. This would require re-converting any and all date variables, but could be a useful workaround. I personally don't like date variables as text but it can come in handy at times.

John Gerstle
CDC Information Technological Support Contract (CITS)
Biostatistician

I recently presented a paper at PHARMASUG 2004 on this very subject.
Paper can be found at:
http://www.lexjansen.com/pharmasug/2004/CodersCorner/CC09.pdf
Thanks to Lex for providing this valuable resource.

Stanley

Whay am I getting this error message? Order_date is a daytime variable

56 proc sql;
57 create table sasdata.june_pre_period as
58 select a.oid_customer,
59 a.total_order_amount,
60 a.order_date,
61 a.store_id,
62 b.offerid,
63 b.campaignid
64 from mic_cdi.orders a
65 left join sasdata.client_july09 b
66 on a.oid_customer=b.oid_customer
67 where ((a.datepart(order_date)>='14JUN2009'd &
_
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *,
**, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT,
NE, NET, OR, ^=, |, ||, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

68 a.datepart(order_date)<='20JUN2009'd) or
69 (a.datepart(order_date)>='26JUL2009'd &
70 a.datepart(order_date)<='01AUG2009'd)) &
71 a.oid_customer in (select oid_customer
72 from sasdata.client_july09
73 where datepart(order_date)
74 datepart
(order_date),='01AUG2009'd
75 );
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of
statements.
76 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

Hi,

I think the statement should be datepart(a.order_date)


On Mon, Sep 7, 2009 at 10:02 AM, William Shakespeare <





Certified Advanced Programmer for SAS V9
Certified Basic Programmer for SAS V9
DataShaping Certified SAS Professional