SAS Character missing to DB2 NULL

SAS Character missing to DB2 NULL

Post by And » Sat, 02 Nov 2002 02:04:46



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?

 
 
 

SAS Character missing to DB2 NULL

Post by Sigurd Hermans » Sat, 02 Nov 2002 04:56:01


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

Sent: Thursday, October 31, 2002 12:05 PM

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?


 
 
 

SAS Character missing to DB2 NULL

Post by Kevin Mye » Sat, 02 Nov 2002 05:35:20


This won't be too much help, but I *thought* that there was an option for
the SAS/Access interface to DB2 where you could tell it to treat blank
values in SAS as NULL values in DB/2.  Perhaps some additional doc reading
or a call to SAS tech support might turn up something...?

s/KAM

----- Original Message -----

Newsgroups: bit.listserv.sas-l

Sent: Thursday, October 31, 2002 1:56 PM
Subject: Re: SAS Character missing to DB2 NULL

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

> Sent: Thursday, October 31, 2002 12:05 PM

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

 
 
 

SAS Character missing to DB2 NULL

Post by Kevin Mye » Sat, 02 Nov 2002 06:10:56


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


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