Date:         Tue, 11 Dec 2001 15:39:44 -0500
Reply-To:     Sigurd Hermansen <hermans1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <hermans1@WESTAT.COM>
Subject:      Re: SQL in SAS and WHERE statements with datatime
Comments: To: Jesus Bermudez <jbermudez@SFP.CIFL.CESGA.ES>
Content-Type: text/plain; charset=ISO-8859-1

Since you are "passing through" a SQL view (sentence) from SAS to a ODBC
database, I suspect that you will have to use ODBC date or timestamp
literals in a WHERE <date type column variable> BETWEEN <date literal> AND
<date literal>. Something along the lines of WHERE datarecollida BETWEEN
{d'19nov1999'} AND {d'8june2000'} might work. (You may also have to put the
curly braces inside string literals to get them past the SAS SQL compiler.)

Documentation of ODBC date variables seems sketchy to me. See

http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/odbc/htm/odbcdate__time__and_timestamp_literals.asp

http://cs1.mcm.edu/tutorial/doc/Adabas/odbc/odbc31.htm#Heading34

Sig
On Tue, 11 Dec 2001 13:28:04 +0100, Jesus Bermudez
<jbermudez@SFP.CIFL.CESGA.ES> wrote:

>hello,
>
>    We are working with SAS System V8 and we want to filter the data(into
SQL sentence) of a data base, so that we have a table with the following
data of the variable "datarecollida":
>19nov1999:00:00:00'; '8jun2000:00:00:00'....
>We are using WHERE statement, but we do not know the syntax for several
data, i.e., we proved with <, >, in, between... but we were not able to
filter the data.
>The format of the data is '14#apr#1999:00:00:00'dt.
>The code that we use (without WHERE statement) is the following:
>
>/*GRAFICAS DE LONG/PARCELA*/
>proc sql;
>connect to ODBC as granell (dsn='granell' );
>create table agullas1 as
>    select * from connection to granell
>        (select novaclav,datarecollida,idade,posicion,lonxitude,id,especie
>from rrodriguez.cont_agullas_lonx);
>run;
>quit;
>
>Any input will be really welcome. Thanks indeed
>
>Jesus Bermudez Garcia
>
>Lourizán Forestry Investigations Center