Date:         Thu, 1 Feb 2001 12:59:31 -0700
Reply-To:     Jack Hamilton <JackHamilton@FIRSTHEALTH.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jack Hamilton <JackHamilton@FIRSTHEALTH.COM>
Subject:      Re: Proc SQL Insert Into
Comments: To: TEDDEMC@shands.ufl.edu
Content-Type: text/plain; charset=us-ascii

Why are you qualifying the variable names?  There's only one data set in use.  It shouldn't make a difference, but you earlier expressed an aversion to unnecessary typing.

What error messages are you now getting?  Try running DESCRIBE TABLE ACHARGE and DESCRIBE TABLE DTARDTLY to make sure the variable attributes are the same in both tables.



--
JackHamilton@FirstHealth.com
Development Manager, Technical Group
METRICS Department, First Health
West Sacramento, California USA


>>> "Matthew Tedder" <TEDDEMC@shands.ufl.edu> 02/01/2001 11:15 AM >>>

Same issues... As I put FROM ACHARGE AS A, and an A. in front of the variable names.... Here is how I created ACHARGE:

/*=====<< OUTPUT ANCILLARY FIGURES >>=====*/
PROC SQL;
    CREATE TABLE ACHARGE  AS
    SELECT DISTINCT
           SUM(A.TRAN_AMT) AS DSMACHRG,
           COUNT(A.TRAN_AMT) AS DCNACHRG,
           POSTDATE
    FROM DTARDLY AS A
    WHERE A.SVC_TYPE = '0'
;

--Matthew

>>> Jack Hamilton <JackHamilton@FIRSTHEALTH.COM> 02/01/01 01:56PM >>>
Are you a DB2 user as well?  I know another DB2 user who also uses that odd (to me) syntax.

Try

   insert into trends.daily
      select  postdate as rundate,
                 DSMACHRG, DSMDCHRG, DSMPYADJ,
                 DCNACHRG, DCNDCHRG, DCNPYADJ
      from    ACHARGE;

But the message you got indicates that either the fields aren't there or they're of different types in the two tables.



--
JackHamilton@FirstHealth.com
Development Manager, Technical Group
METRICS Department, First Health
West Sacramento, California USA


>>> "Matthew Tedder" <TEDDEMC@SHANDS.UFL.EDU> 02/01/2001 10:27 AM >>>

Actually, the field names are identical in both tables.  The table I'm
trying to insert into was created with a CREATE TABLE statement earlier
but has no data yet.  The source of the data I'm trying to populate into it
is a SAS dataset stored on DASD.  Here's the error messages from the
actual SASLOG.  It basically says it doesn't recognize the variable
names as being within A.  My 3270 terminal cuts of the right portion of these error lines but they all end with:  le/view identified with the correlation name A.
==================<< SASLOG BELOW >>================
NOTE: The PROCEDURE SQL used 0.48 CPU seconds and 9996K.

227        PROC SQL;
228            INSERT INTO TRENDS.DAILY
229            (
230              RUNDATE,
231              DSMACHRG, DSMDCHRG, DSMPYADJ,
232              DCNACHRG, DCNDCHRG, DCNPYADJ
233            )
234           ,SELECT
235              A.POSTDATE,
236              A.DSMACHRG, A.DSMDCHRG, A.DSMPYADJ,
237              A.DCNACHRG, A.DCNDCHRG, A.DCNPYADJ
238            FROM
239                 ACHARGE AS A
240        ;
ERROR: Column DSMDCHRG could not be found in the table/view identified with the
ERROR: Column DSMDCHRG could not be found in the table/view identified with the
ERROR: Column DSMDCHRG could not be found in the table/view identified with the
ERROR: Unresolved reference to table/correlation name A.
ERROR: Unresolved reference to table/correlation name A.
ERROR: Unresolved reference to table/correlation name A.
ERROR: Column DSMPYADJ could not be found in the table/view identified with the
ERROR: Column DSMPYADJ could not be found in the table/view identified with the
ERROR: Column DSMPYADJ could not be found in the table/view identified with the
ERROR: Unresolved reference to table/correlation name A.
ERROR: Unresolved reference to table/correlation name A.
ERROR: Unresolved reference to table/correlation name A.
ERROR: Column DCNDCHRG could not be found in the table/view identified with the
ERROR: Column DCNDCHRG could not be found in the table/view identified with the
ERROR: Column DCNDCHRG could not be found in the table/view identified with the
ERROR: Unresolved reference to table/correlation name A.
ERROR: Unresolved reference to table/correlation name A.
ERROR: Unresolved reference to table/correlation name A.
ERROR: Column DCNPYADJ could not be found in the table/view identified with the
ERROR: Column DCNPYADJ could not be found in the table/view identified with the
ERROR: Column DCNPYADJ could not be found in the table/view identified with the
ERROR: Unresolved reference to table/correlation name A.
ERROR: Unresolved reference to table/correlation name A.
ERROR: Unresolved reference to table/correlation name A.
ERROR: Value 3 on the SELECT clause does not match the data type of the correspo
ERROR: Value 3 on the SELECT clause does not match the data type of the correspo
ERROR: Value 3 on the SELECT clause does not match the data type of the correspo
ERROR: Value 4 on the SELECT clause does not match the data type of the correspo
ERROR: Value 4 on the SELECT clause does not match the data type of the correspo
ERROR: Value 4 on the SELECT clause does not match the data type of the correspo
ERROR: Value 6 on the SELECT clause does not match the data type of the correspo
ERROR: Value 6 on the SELECT clause does not match the data type of the correspo
ERROR: Value 6 on the SELECT clause does not match the data type of the correspo
ERROR: Value 7 on the SELECT clause does not match the data type of the correspo
ERROR: Value 7 on the SELECT clause does not match the data type of the correspo
ERROR: Value 7 on the SELECT clause does not match the data type of the correspo
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statem
241
NOTE: The SAS System stopped processing this step because of errors.
NOTE: The PROCEDURE SQL used 0.03 CPU seconds and 10006K.