Erro Primary_Key

[Amanda_31]

Ola pessoal, 

Poderiam me ajudar no erro abaixo, sabem em que local posso encontrar o erro nas minhas tabelas? Eu já fiz as chaves que constam no programa mas eu não encontro duplicidade de informação. Obrigada pela ajuda.

NOTE: Mapping columns ...
MPRINT(ETLS_LOADER): proc sql;
MPRINT(ETLS_LOADER): create view work.W37MYOB as select DATA_REPORTE as REPORTING_DT format = DATE9. informat = DATE9.,
TIPO_ENTIDADE as ENTITY_ID, GRUPO_IFRS17 as INSURANCE_CONTRACT_GROUP_ID, TAG as CASHFLOW_LEG_NM, (CASE WHEN (RESSEGURO_FLG) ="Sim"
THEN "Y" ELSE "N" END) as CEDED_FLG length = 1, TIPO_FLUXO as CASHFLOW_TYPE_CD length = 10, TIPO_MOEDA as CURRENCY_CD, (case when
(DATA_OCORRENCIA) ne . then DATA_OCORRENCIA else 0 end) as INCURRED_CLAIM_DT length = 8 format = DATE9. informat = DATE9.,
DATA_VENCIMENTO_X_PAGAMENTO as CASHFLOW_DT format = DATE9. informat = DATE9., VALOR as CASHFLOW_AMT format = NLNUM18.5 informat =
NLNUM18.5 from WORK.WRK_PAA_TIPO_EXECUCAO_PROD ;
NOTE: SQL view WORK.W37MYOB has been defined.
MPRINT(ETLS_LOADER): quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.01 seconds
memory 508.40k
OS Memory 29856.00k
Timestamp 10/27/2023 08:27:27 PM
Step Count 11232 Switch Count 2
Page Faults 0
Page Reclaims 70
Page Swaps 0
Voluntary Context Switches 11
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 24

NOTE: Appending data ...
MPRINT(ETLS_LOADER): proc append base = IFRSSDM.INSURANCE_CASHFLOW data = WORK.W37MYOB () force ;
MPRINT(ETLS_LOADER): run;

NOTE: Appending WORK.W37MYOB to IFRSSDM.INSURANCE_CASHFLOW.
WARNING: Add/Update failed for data set IFRSSDM.INSURANCE_CASHFLOW because data value(s) do not comply with integrity constraint
PRIM_KEY. (Occurred 6 times.)

[Patrick · Accepted solution]

Question translated to English via chatGPT

Could you help me with the error below? Do you know where I can find the error in my tables? I've already created the keys as specified in the program, but I can't find any duplicate information. Thank you for your help.

Your master table IFRSSDM.INSURANCE_CASHFLOW got a primary key integrity constraint defined. Which variables are part of this primary key is visible in the table metadata definition. If the table already exists physically then you can also query it using for example Proc Contents. Below an example for a primary key constraint created over a variable with name key_var

proc contents data=work.master;
run;

SAS hits the integrity constraint and throws the warning because you are trying to insert (append) rows from your transaction table work.W37MYOB with key values that already exist in the master table. 

If your DIS job doesn't create the master table and you re-run your DIS job then it's possible that you hit this issue simply because you already loaded the new transactions in an earlier run.

When using Proc Append with SAS datasets then SAS will still insert all rows that don't hit the integrity constraint. If you would use a Proc SQL insert then it would be an Error and the rows wouldn't get inserted. You would also get an error if the master table would be a database table as then the database would throw the error.

If you want to ensure that you never get such a warning in any case then you need first to work out the delta. You could alternatively also try the DIS SQL Merge transformation and just do nothing in case of a match (not sure if the transformation allows for this).

Below some sample code to replicate what you observe.

/* create sample data */
data work.master;
  input key_var value_var $;
  datalines;
1 A
2 A
;

data work.transaction;
  input key_var value_var $;
  datalines;
2 X
5 X
6 X
;

/* add primary key to sample data */
proc datasets lib=work nolist;
  modify master;
  ic create prim_key=primary key(key_var);
  run;
quit;

/* replicate the warning by appending/inserting the rows */
proc append base=work.master data=work.transaction;
run;quit;

NOTE: Appending WORK.TRANSACTION to WORK.MASTER.
WARNING: Add/Update failed for data set WORK.MASTER because data value(s) do not comply with integrity constraint prim_key, 1 
observations rejected.

You get the warning because key value 2 in the transaction table already exists in the master table. 

The log also tells you that it rejected 1 row which means the other two rows where there hasn't been a conflict got inserted.

If you re-run the Proc Append on its own then the SAS log will tell you that 3 rows have been rejected.

If you want to ensure that you're only ever loading new transactions then you need first to identify the delta. This of course is additional processing with some performance impact. In below code table work.transaction_delta must be a table and not a view.

proc sql;
  create table work.transaction_delta as
  select t.key_var, t.value_var
  from 
    work.transaction as t
    left join
    work.master m
      on t.key_var=m.key_var
    having missing(m.key_var)
  ;
quit;
proc append base=work.master data=work.transaction_delta;
run;quit;

And as already said earlier an alternative to above code could be the DIS SQL Merge transformation. I never used it so can't tell if it allows you to define a "no action" in case of a key match (the update case).

From a performance perspective a Proc Append will be fastest. If you can be sure that you never get "old" transactions from source and that you only hit the integrity constraint in case of a re-run then I'd consider the Warning as acceptable and you could document it as something that can happen during development/re-run but that it is of no substantial concern in a Prod environment. What's the right thing to do really depends on how important performance is and if you can be certain that you only ever get the delta transactions from source (or already worked out the delta earlier).

And last but not least:

I recommend to not use views but only tables during DIS development. Only switch over to views when things work for a last test.

The reason is that with views you only get the errors in the node where the view executes and not where you define it which can make debugging quite a bit harder.