Date:         Sun, 4 Apr 2010 13:02:39 -0500
Reply-To:     Craig Johnson <cjohns38@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Craig Johnson <cjohns38@GMAIL.COM>
Subject:      Re: Two Proc SQL Questions (Easy for an expert)
Comments: To: Jonathan Goldberg <jgoldberg@biomedsys.com>
In-Reply-To:  <201004022325.o32Apggh030351@malibu.cc.uga.edu>
Content-Type: text/plain; charset=ISO-8859-1

Hi Johnathan,

Thanks for the insight into the &Sysdate as a string and the example SQL
query.  It's taken a long time but I'm getting close to having an actual
working system and that is exciting.

I'll keep looking for a way to get SAS and Access to play nicely with
dates.

Thanks again

On Fri, Apr 2, 2010 at 6:25 PM, Jonathan Goldberg
<jgoldberg@biomedsys.com>wrote:

> I will not venture tell you how to give Access a date format it will like,
> because I've forgotten how it treats dates.  However, I can tell you
> what's wrong here:
>
>        WHEN ERRORID NE '' THEN '&Sysdate'
> >
> >            Else *1*/*1*/*1900*
>
> '&Sysdate' is a string.  &Sysdate resolves to a string.  So SQL thinks
> you're creating a string.  I'm assuming "*1*/*1*/*1900*"is a SAS date value
> that got mangled somehow, and a SAS date value is a number.  Hence the
> error message.
>
> On question two:
>
> Yes, you can do this.  The method is to put a where clause on the select
> that generates your insertions that queries ACCESSDB.FORMERRORSANDFIXES,
> the table you're inserting into.
>
> Something like this:
>   INSERT INTO ACCESSDB.FORMERRORSANDFIXES
>   (select your-list-of-fields
>    from &sysday.errors e
>    where (select count(*) from ACCESSDB.FORMERRORSANDFIXES f,
>           where
>           e.id = f.id and
>           e.errorid = f.errorid) = 0   /*combination not found*/
>    )
>
> Cheers,
>
> Jonathan
>
> This is called a correlated subquery, because the inner query needs to be
> executed for each row of the outer query.
>
> On Thu, 1 Apr 2010 10:54:14 -0500, Craig Johnson <cjohns38@GMAIL.COM>
> wrote:
>
> >1)      I�m running code that finds and outputs errors to temp SAS file.
> The
> >temp error file includes and ERRORID, ID, FILENAME, FILEID, FIELD, and
> >ERROR. Once the errors our found I would like to insert them into a
> database
> >for tracking and fixing.  The access table variables include ERRORID, ID,
> >FILENAME, FILEID, FIELD, ERROR, DATEFOUND, SOLUTION, and NOTES.  I�m
> >defaulting the Solution and Error to null on the insert but I�m having a
> >hard time defaulting the Datefound using the &SYSDATE or &SYSDATE9.
> >Basically the time formats of SAS and access aren�t matching up and I'm
> not
> >sure how to get them to play nicely. Plus, I'm not sure how to quote or
> not
> >quote to get it to insert properly.
> >
> >
> >SAS code I�m working with:
> >
> >
> >
> >*PROC* *SQL*;
> >
> >INSERT INTO ACCESSDB.FORMERRORSANDFIXES
> >
> >SELECT ERRORID, ID, FILENAME, FIELDID, FIELD, ERROR,DATEFOUND,
> >
> >      CASE
> >
> >            WHEN ERRORID NE '' THEN ''
> >
> >            ELSE 'ERROR!'
> >
> >      END AS Solution,
> >
> >      CASE
> >
> >            WHEN ERRORID NE '' THEN ''
> >
> >            ELSE 'ERROR!'
> >
> >      END AS Notes,
> >
> >      CASE
> >
> >            WHEN ERRORID NE '' THEN '&Sysdate'
> >
> >            Else *1*/*1*/*1900*
> >
> >      END AS DateFound
> >
> >FROM &SYSDAY._ERRORS;
> >
> >*QUIT*;
> >
> >
> >
> >When I don�t put the &SYSDATE macros in quotes I get:
> >
> >
> >
> >NOTE: Line generated by the macro variable "SYSDATE".
> >
> >1       01APR10
> >
> >          -----
> >
> >          22
> >
> >          76
> >
> >ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *,
> **,
> >+, -, /, <, <=, <>,
> >
> >              =, >, >=, ?, AND, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE,
> >LET, LIKE, LT, LTT, NE,
> >
> >              NET, OR, ^=, |, ||, ~=.
> >
> >
> >
> >ERROR 76-322: Syntax error, statement will be ignored.
> >
> >
> >
> >When I do put quotes I get:
> >
> >
> >
> >ERROR: Result of WHEN clause 2 is not the same data type as the preceding
> >results.
> >
> >ERROR: Attempt to insert more columns than specified after the INSERT
> table
> >name.
> >
> >ERROR: Value 7 on the SELECT clause does not match the data type of the
> >corresponding column
> >
> >       listed after the INSERT table name.
> >
> >ERROR: Value 9 on the SELECT clause does not match the data type of the
> >corresponding column listed after the INSERT table name.
> >
> >
> >
> >2)  Is it possible to do an insert using one-step where the query only
> >inserts if the ErrorID and ID combination are not already in the table?
> Sort
> >of a conditional SQL insert statement?
> >
> >
> >Thanks
>