Date:         Tue, 5 Feb 2008 09:03:10 -0800
Reply-To:     "Nordlund, Dan (DSHS/RDA)" <NordlDJ@DSHS.WA.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Nordlund, Dan (DSHS/RDA)" <NordlDJ@DSHS.WA.GOV>
Subject:      Re: proc sql :An unexpected token "D" was found
In-Reply-To:  <96e13f29-f1f0-4171-bb47-5ea30bd8814b@d70g2000hsb.googlegroups.com>
Content-Type: text/plain; charset=iso-8859-1

> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On
> Behalf Of shaunak.adgaonkar@gmail.com
> Sent: Tuesday, February 05, 2008 8:14 AM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: proc sql :An unexpected token "D" was found
>
> What is wrong with this code ? I know that the usual sql code doesnt
> work in proc sql and for that i need to use sysfunc but how to add
> that in sql pass through code her. Here is my code
>
> PROC
> SQL;
> connect to db2 (DB=HOACD011
> SCHEMA='MEMBER'
>         USER=&UID
> PASSWORD=&PWD);
> CREATE TABLE comp.ods_all_records2
> AS
> select * from connection to db2
>
> (SELECT
>         A.CRCT_IND
>         ,A.STRG_NET_WRTH_DT
>         ,A.SUTL_RGTR_STRT_DT
>
>               ,CASE
> A.SUTL_RGTR_STS_DT
>           WHEN '01JAN0001'D THEN 1
>        END AS
> RSDT
>       ,CASE
> A.STRG_ANN_INCM_DT
>           WHEN '01JAN0001'D THEN
> 1
>        END AS
> AIDT
>       ,CASE
> A.STRG_NET_WRTH_DT
>           WHEN '01JAN0001'D THEN
> 1
>        END AS
> NWDT
>       ,CASE
> A.INVS_OBJ_DT
>           WHEN '01JAN0001'D THEN
> 1
>        END AS
> IODT
>       ,CASE
> A.SUTL_RGTR_STRT_DT
>           WHEN '01JAN0001'D THEN
> 1
>        END AS STDT
>
>
>  FROM ods.table1 AS A
>  where where mod(&MATCH_KEY,10)=1
>  );
> quit;
>

I don't enough about all dialects of SQL to give you specific code, but the solution to your error message is to use the appropriate SQL function for your implementation to turn a string into a date.  When I query an Oracle database I use the TO_DATE() function (you do realize that in SAS your date will be interpreted using the currently set cutoff value, not as a date in the first century?)

  TO_DATE('jan-01-2001', 'mon-dd-yyyy')

So, look at the date functions in your SQL implementation.  Hope this is helpful,

Dan

Daniel J. Nordlund
Research and Data Analysis
Washington State Department of Social and Health Services
Olympia, WA  98504-5204