SAS Character missing to DB2 NULL

SAS Character missing to DB2 NULL

Post by And » Mon, 11 Nov 2002 03:42:35



Thanks to all for your help. Your contributions were useful.

For the final solution, I ended up constructing a dynamic macro to pass
UPDATE statements to DB2 via the EXECUTE command in PROC SQL.

I *was* able to get the NULLCHAR/NULLCHARVAL options suggested by Kevin
Myers to work, but only by replacing the entire table, which was too
expensive to do on a nightly basis. However, this is appears to be the
best method to get null values into DB2 in most situations.

The CASE statement did not work in this particular circumstance because
there was no way to associate it with the individual observations being
inserted. Also the column lengths are dictacted by the table structure,
which is controlled by the DB2 DBAs, and often have a length less than 4.

The article forwarded by Mike Zdeb has a wealth of good info on dealing
with nulls in SAS. From a SAS perspective, there are more plusses than
minuses to having blanks and nulls coalesced.



>I found the following info concerning the NULLCHAR= data set option in the
>Online Help.  Hope this helps.
>s/KAM

>NULLCHAR=
>--------------------------------------------------------------------------
--
>----

>The NULLCHAR= data set option indicates how SAS character missing values
are
>handled during insert, update, DBINDEX=, and DBKEY= processing.
>Valid in: DATA and PROC steps (when accessing DBMS data using SAS/ACCESS
>software)
>DBMS support: CA-OpenIngres, DB2 OS/390, DB2 Unix/PC, DB2 VM, Informix,
>ODBC, OLE DB, ORACLE, Oracle Rdb, SQL Server, SYBASE, Teradata
>Default value: SAS

>--------------------------------------------------------------------------
--
>----

>Syntax
>NULLCHAR= SAS | YES | NO

>Syntax Description

>SAS
>indicates that character missing values in SAS data sets are treated as
NULL
>values if the DBMS allows NULLs. Otherwise, character missing values are
>treated as the NULLCHARVAL= value.

>YES
>indicates that character missing values in SAS data sets are treated as
NULL
>values if the DBMS allows NULLs. Otherwise, an error is returned.

>NO
>indicates that character missing values in SAS data sets are treated as
the
>NULLCHARVAL= value (regardless of whether the DBMS allows NULLs for the
>column).

>--------------------------------------------------------------------------
--
>----

>Details

>This option affects insert and update processing and also applies when you
>use the DBINDEX= and DBKEY= options.

>This option works in conjunction with the NULLCHARVAL= data set option,
>which determines what is inserted when NULL values are not allowed.

>All SAS numeric missing values (represented in SAS as '.') are treated by
>the DBMS as NULLs.

>ORACLE Details: See ORACLE BULKLOAD Interactions with Other Options for
>interactions between NULLCHAR and BULKLOAD=.
>--------------------------------------------------------------------------
--
>----

>See Also

> NULLCHARVAL=
> DBNULL=

>----- Original Message -----




- Show quoted text -

Quote:>Sent: Thursday, October 31, 2002 2:49 PM
>Subject: Re: SAS Character missing to DB2 NULL

>> Hi.  Only thing I've been able to find on NULLS and SQL...

>> (See attached file: nulls.pdf)

>> Mike Zdeb
>> New York State Department of Health
>> ESP Tower - Room 1811
>> Albany, NY   12237
>> P/518-473-2855  F/630-604-1475

 
 
 

1. 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?

2. Irix6.5 X base desktop installation problem

3. options missing / set null as missing

4. B&W printing on a Canon S520

5. Convert DB2 Code to SAS/ACCESS to DB2 Code in the MVS

6. 14.4 modems: review articles

7. Problem distilling pc postscript (error null null null...)

8. Help, trying to connect to Cisco 2511 using HP's DTC

9. Inserting NULL in DB2 DECIMAL Field

10. Sending Character Buffers to external DLLs (with nulls)

11. Vi: How to insert control character 0 (null) ?

12. Is /dev/null a block or character device?