topic Re: Risk Modeling, configuring subset maps in SAS Risk Management

Risk Modeling, configuring subset maps

beste — Wed, 02 Mar 2022 21:18:59 GMT

Hi Everyone,

I'm trying to update subset maps in the risk modeling solution.

I'm on the step to configure joins.

This is the code that I've used:

proc sql;
insert into apdm.Subset_Table_Join_Condition
(Subset_Table_Join_Condition_Sk, Subset_From_Path_Sk, 
Join_Condition_Sequence_Number, Join_Type, 
Left_Table_Sk, Left_Column_Sk, Right_Table_Sk, 
Right_Column_Sk, created_dttm, 
created_by_user, last_processed_dttm, last_processed_by_user)
values(1111114, 13, 1, 'LEFT', 1100013, 1100014, 1100015, 1100016, "%sysfunc(datetime(),DATETIME.)"dt,
"&sysuserid", "%sysfunc(datetime(),DATETIME.)"dt,"sysuserid");
quit;

I get the error below:

ERROR: During insert: ERROR: insert or update on table "subset_table_join_condition" violates foreign key constraint 
       "subset_table_join_condition_left_column_sk_fkey"
DETAIL: Key (left_column_sk)=(1100014) is not present in table

I checked the table and I know for a fact that left_column_sk exists in the table. When I tried to omit it, it wouldn't let me do that either because of the null constraint. What am I doing wrong here?

Thanks.

@sasprogramming

Re: Risk Modeling, configuring subset maps

BrendanW — Wed, 02 Mar 2022 21:40:13 GMT

Have you checked to make sure that the specific key the join is looking for, 1100014 exists for that column?

Re: Risk Modeling, configuring subset maps

beste — Wed, 02 Mar 2022 21:48:45 GMT

I’m not sure if I interpreted you correctly. This is a new record, so I’m trying to add that value. Was this what you were asking for?

Thanks

Re: Risk Modeling, configuring subset maps

BrendanW — Wed, 02 Mar 2022 22:45:03 GMT

If I understand this correctly, you are trying to insert a row, but if the column is referencing a column in another table (a foreign key), that key must exist in the other table. If the key doesn't exist in that table, then you'll get a foreign key violation.

I'm unfortunately not specifically familiar with this product.

Re: Risk Modeling, configuring subset maps

beste — Wed, 02 Mar 2022 22:49:45 GMT

Yes, I understand that. But the key exists in the table that im trying to insert. I’m just trying to put a value to the respecting key

Re: Risk Modeling, configuring subset maps

BrendanW — Wed, 02 Mar 2022 23:04:24 GMT

Do the data types match for both between the tables? For example, NUMERIC(8) i believe is the data type that the columns should be using.

Re: Risk Modeling, configuring subset maps

beste — Thu, 03 Mar 2022 14:25:45 GMT

I’m not sure what you mean by that. There is no mismatch of Char-Numeric though. All columns have proper entries. When a mismatch happens another error pops up telling me there is a mismatch between the columns.