Date:         Sun, 25 Sep 2005 14:30:30 +0000
Reply-To:     toby dunn <tobydunn@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         toby dunn <tobydunn@HOTMAIL.COM>
Subject:      Re: Using SAS Date In SQL
Comments: To: amir@PROGRAMMER.NET
In-Reply-To:  <1127634773.099634.66740@f14g2000cwb.googlegroups.com>
Content-Type: text/plain; format=flowed

khan,

Dates and time are an artificial construct of humans.  While it is extremely
handy and easy for us humans to understand dates like 01JAN2005 computers
have a hard time with such a construct when something other than printing is
needed.  So the common and SAS way of handling it is through converting the
human form of the date into a scaler, ussually done through SAS with the use
of informats.  There by allowing the computer to make a simple mathmatical
adjustment up or down when the date needs adjusting or compared to another
date or shown in a different way.  However, this scaler or SAS Date is an
abstraction of an abstract concept.  Therefore when the date needs to be
printed either on paper or the screen SAS uses formats.  Now what does all
this mean in conjunction with your problem.

Well you have a SAS date value which is a scaler (i.e. number) and you are
giving it a text constant to compare to.  Whiich as you are finding out
dowsnt fly.  So what you need to do is either compare the variable to the
numeric number that represents the date you want or use the suffix 'd' or
'dt' so that sas knows to convert the text string in date9 or datetime
format into a SAS date (scaler).

Now since a SAS Date is by default the number of days since Jan 1, 1960 and
SAS DateTime is the number of seconds since Midnight on Jan 1, 1960 the two
will never compare without a little help from some date functions.


data _null_ ;

SASDate        = '01Jan2005'd  ;
SASDateTime = '01Jan2005:0:00:00'dt ;

Put '01Jan2005' +1 SASDate= +1 SASDateTime= ;


if SASDate = SASDateTime then put 'They Match' ;
run ;


Now if say you have a date time variable and you want to compare just the
date part of it to another sas date value you need to extract the date
portion of the date time value.

data _null_ ;

SASDate        = '01Jan2005'd  ;
SASDateTime = '01Jan2005:0:00:00'dt ;

if SASDate = datepart(SASDateTime) then put 'They Match' ;
run ;



The question now you have to ask is the time part of the datatime variable
you have in your data set important or not.


Toby Dunn





From: Khan <amir@PROGRAMMER.NET>
Reply-To: Khan <amir@PROGRAMMER.NET>
To: SAS-L@LISTSERV.UGA.EDU
Subject: Using SAS Date In SQL
Date: Sun, 25 Sep 2005 00:52:53 -0700

Hi all,
I converted a MSAccess DB to sas dataset, in that dadaset one date
field has datetime19. format and informat.
Using sql query in where clause it is not accepting the parameter.

for example "select loanno1 from work.sales where i_send1 =
'20SEP2005';"

Kindly give some solution.