Date:         Thu, 31 Oct 2002 14:56:01 -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: SAS Character missing to DB2 NULL
Comments: To: Andy <slap_u_silly_2000@YAHOO.COM>
Content-Type: text/plain; charset="iso-8859-1"

How would you insert a NULL in a DB2 column if you sent the query directly
to DB2? Probably by inserting a reserved keyword, NULL.... (DB2 stores
NULL's as values outside the domains of column variables, so SAS cannot
insert the DB2 value directly.)

For character variables you might want to try expanding the select list and
adding
...,case when x is missing then 'NULL'n else x end as x .... Make sure that
the column variables being treated this way have length 4 or greater. The
string literal 'NULL'n will pass the string 'NULL' thru unaltered to DB2. It
will not treat NULL as a variable name.

Of course you will not be able to differentiate NULL's from MISSING's in
SAS, so you will have to treat all missing values in each column either as
blanks or NULL's.

Sig

-----Original Message-----
From: Andy [mailto:slap_u_silly_2000@YAHOO.COM]
Sent: Thursday, October 31, 2002 12:05 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: SAS Character missing to DB2 NULL


Is there a way in SAS to insert NULL character values into a DB2 table?

I am writing a SAS program that does a nightly update to tables in a DB2
warehouse. The tables are large, so I want to just INSERT the new rows,
rather than reload the entire table.

The problem is that for character values, DB2 distinguishes a difference
between blanks and NULLs and SAS does not. Using the code below, SAS
updates the table, but inserts blanks into the DB2 table, not NULL:

LIBNAME DB2WH DB2 SSID=DB2 AUTHID=WH1;

PROC SQL;
  INSERT INTO DB2WH.&TBLNAME.
  SELECT *
  FROM SAS.&TBLNAME.
  ;
QUIT;

Any suggestions?