Creating DB2 table in SAS adds missing values

Question

I ran into this problem when I tried to upload a list of members to our DB2 server, and have created a minimal example to explain the problem.

In order to join locally-created data tables from SAS 9.2 to tables in our DB2 data warehouse (accessed with a connection to an ODBC driver), my procedure has been to place those local tables on the same server (different schema) that houses the claims. The primary purpose of this is to filter out a list of claims where the member_ID is contained in the uploaded list.

If there are missing values in the first row of the local data (sometimes a member might not have a piece of identifying information), the data on the local side loses several non-missing values when it gets uploaded to the DB2 schema. I've attached a toy example with non-sensitive information to highlight the problem.

PROC IMPORT OUT= WORK.druglist DATAFILE= "C:\Users\caden2\Desktop\druglist.xls" 
DBMS=EXCEL REPLACE; 
RANGE="'Tab 4# Asthma Meds$'"; 
RUN;

DATA for_clinical;
SET druglist(KEEP= Drug_Class Drug_Type Generic GPI);
IF _N_ = 4 THEN DELETE;
IF _N_ <= 10;
IF _N_ = 1 THEN DO;
    Drug_Class=''; 
    Drug_Type='';
    Generic='';
    END;
RUN;

libname clinic odbc user=XXX password=XXX dsn=DWName schema=DWSchema autocommit=yes;

PROC SQL;
DROP TABLE clinic.caden_test;
CREATE TABLE clinic.caden_test AS
    SELECT * FROM for_clinical;
QUIT;

The PROC IMPORT and Data step are simply to get the data into the form I need, and should not have any issues. The clinical library is created through an ODBC connection to our data warehouse with my credentials. The screenshot below shows a before and after of the data set on the local machine and the clinical server (notice the first row has some missingness). I didn't post the log. What is scary is that my log provided no indication that anything was wrong.

enter image description here

Is this a problem with DB2, SAS, or a combination of SAS and DB2? I am not able to replicate the problem going from local-to-local or DB2-to-DB2. Additionally, the removed values always appear in a diagonal formation. With wider data (more than 4 variables), it starts at a random row and proceeds diagonally up and to the right until it reaches the last column.

To address BellevueBob's concern in the comments that it is not Viewtable messing up, here is the output of a PROC FREQ on the clinical data, showing that the missingness is indeed there. enter image description here

BellevueBob's answer works for the case when the missing data is character, but creating a data table in the same fashion, but with numeric data, the solution does not work like how it does with character data.

I wasn't sure whether this was a bug with SAS, DB2, or PEBKAC, so I tagged the first two. — Christopher Aden
Can you modify your question to add the complete SAS libname statement used for clinic? Also, are you sure the DB2 table itself has the incorrect column values? viewtable is notoriously picky. — BellevueBob
I've updated the description to highlight your concerns. I've censored out my username, password, dsn and schema, but the statement is a pretty vanilla ODBC declaration. The SQL call doesn't give any indication that "clinic" is a remote library or a local one. — Christopher Aden

BellevueBob BellevueBob · Accepted Answer · 2013-04-19T00:41:29

Try using the NULLCHAR=NO data set option:

PROC SQL;
   DROP TABLE clinic.caden_test;
   CREATE TABLE clinic.caden_test(NULLCHAR=NO) AS
   SELECT * FROM for_clinical3;
QUIT;

Here is a SAS reference.

I have not used DB2 in quite a while and don't know how tables are created through ODBC. However, I'd also investigate the other available data set options, in particular DBCREATE_TABLE_OPTS and DBTYPE, which let you explicitly define the table index and column types.

EDIT: Corrected answer based on OP comments.

The NULLCHAR=NO option tells SAS to send a single-blank character to the database when inserting missing values rather than setting the value to a NULL. Although this works, it is probably worthy of a SAS usage note.

Creating DB2 table in SAS adds missing values

2 Answers