topic Re: Trying to insert 10k data into DB2 table from SAS EG in New SAS User

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

animesh123 — Mon, 21 Oct 2024 10:04:58 GMT

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

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

yabwon — Mon, 21 Oct 2024 10:27:18 GMT

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

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

animesh123 — Mon, 21 Oct 2024 11:34:46 GMT

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

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

yabwon — Mon, 21 Oct 2024 11:48:03 GMT

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

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

animesh123 — Mon, 21 Oct 2024 12:38:57 GMT

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

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

yabwon — Mon, 21 Oct 2024 14:08:08 GMT

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

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

animesh123 — Tue, 22 Oct 2024 09:18:17 GMT

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 ?

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

yabwon — Tue, 22 Oct 2024 10:44:48 GMT

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

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

animesh123 — Tue, 22 Oct 2024 11:13:59 GMT

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

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

Patrick — Tue, 22 Oct 2024 11:54:28 GMT

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;

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

animesh123 — Wed, 23 Oct 2024 11:05:57 GMT

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

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

Kurt_Bremser — Wed, 23 Oct 2024 17:49:27 GMT

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.