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