Trying to insert 10k data into DB2 table from SAS EG

[animesh123]

Hi - I am trying to insert 10k data into DB2 table from SAS EG  ran 

Proc append showing error 

 ERROR: CLI execute error: [IBM][CLI Driver][DB2] SQL0407N Assignment of a NULL value to a NOT NULL column "*N" is not allowed.
SQLSTATE=23502

Tried proc copy showing the same error 

is there a way we can use proc sql for this 

[yabwon]

From the error it looks like you are trying to insert missing value to a column with "no missing allowed" constraint.

Are you sure there are no missing data in your "to-be-inserted" table?

Bart

[animesh123]

yes there is a missing value , is there a way to handle this missing value or handle this type of error .

[yabwon]

Options that came to my head "right away":

1) if you have DB2 admin rights for the table (and it's not a business critical one but a temporary) then do "alter table" [but I'm wiling to bet $5 that this is not the case]

2) remove observations with missing values from the transaction table (e.g. the WHERE= dataset option, `WHERE=(<variableName> is not null)`)

3) replace missing values with proper values that suppose to be there

Bart

[animesh123]

Yes I dont want replace missing value , the reason i want to use proc append so it just get append

[yabwon]

It won't "just get append" because your input data violates database restrictions...

[animesh123]

So since I have fix this missing value issue with below code 

if cmiss(char_vars[i]) then char_vars[i]=' ';
if nmiss(num_vars[j]) then num_vars[j]='1';

Now as I tried to do the proc append am getting different error 

ERROR: CLI execute error: [IBM][CLI Driver][DB2] SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign
key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified
by "YGRT1KL1" constrains table "0000004201" from having duplicate values for the index key. SQLSTATE=23505.

Is there are any fix other than this ?

[yabwon]

Yes, remove observations that violates "uniqueness" of  primary key, exactly as the error note says:

"because the primary key, unique constraint or unique index identified
by "YGRT1KL1" constrains table "0000004201" from having duplicate values for the index key"

The data you prepared have duplicates over data already in the table.

Are you sure you want to "append" that data? Maybe you need to do an "update"?

Bart

[animesh123]

I have like 70 variable and near about 1.3M Obs ,
using update statement I have to define all the variable .correct ?

[Patrick]

SAS doesn't have a concept of NULL values. In SAS a character missing results in a blank on the DB side so I believe you only need to change missing numerical values to some value.

The issue with the primary/unique key violation means that either your source data doesn't comply with the target table definition - like if you've got multiple rows in your source data with the same key value - or that you need to load via update/insert. 

DB2 got a MERGE statement that allows for update/insert via a single SQL. This of course requires you to write explicit pass-through SQL. A common way is to load your source data in SAS first into a staging table (can be a temporary DB2 table) and then run the upsert on the DB2 via explicit pass-through SQL.

I just copy/pasted the following prompts into Copilot and the answer with sample code looks pretty much o.k. to me.

sample code SAS table temporary DB2 staging table upsert DB2 target table

And yes, you need to type all the variables but you can of course generate this list and then just amend it in a text editor. I myself use often Notepad++ with RegEx search & replace for such tasks.

One quick way to just create the list of variables in the SAS log - in below sample for sashelp.class:

options ls=max ps=max;
proc sql feedback noexec;
  select * 
  from sashelp.class c
  ;
quit;

[animesh123]

Hey Guys , 

So after fixing the duplicate and missing value the append code were working fine for 10bs ,

But now since I tried to process the total (83.7 mil) data  , its throwing error 

ERROR: CLI execute error: [IBM][CLI Driver] CLI0111E Numeric value out of range. SQLSTATE=22003

[Kurt_Bremser]

Get the documentation of the target table, and then check your SAS dataset against it. Pay particular attention to numeric columns defined as integer and/or with less than 8 bytes. Run PROC MEANS on these columns and get the MIN and MAX statistics.