Need to conduct validation for values appearing in a data table against a master table?

[axel_p]

I have 2 tables called collateral and collateral type master in an SQL server database which I'm accessing through SAS Enterprise guide

Both have two columns COLLATERAL_TYPE_CODE & COLLATERAL_SUB_TYPE_CODE , the combination of which i need to validate against entries in the master, and only for reporting date 31st December 2021 records

Eg - (COLLATERAL_TYPE_CODE, COLLATERAL_SUB_TYPE_CODE) = (70,70) , (70,701) , etc

The issue is that the data type for the fields should be numeric as per the data template but as per the structure (definition ? ) of the table in the database , only COLLATERAL_TYPE_CODE is numeric, as it should be, in the collateral table but the rest are character.

I need help with a query that can help me check out / validate the values appearing in the data table against the master

The code that i have tried till now

/*COLLATERAL_TYPE_CODE*/
/*COLLATERAL_SUB_TYPE_CODE*/



proc sql;
/*create table temp_1 as*/
select count(1)as cnt, COLLATERAL_TYPE_CODE
from prcr.TB_IFRS9_COLLATERAL_TYPE_MAS
group by COLLATERAL_TYPE_CODE
order by COLLATERAL_TYPE_CODE;
quit;



proc sql;
create table temp_1 as
select distinct (COLLATERAL_SUB_TYPE_CODE,COLLATERAL_TYPE_CODE)
from prcr.TB_IFRS9_COLLATERAL
where REPORTING_DATE in ("31DEC2021"d) ;
quit;



data temp_2;
set temp_1;
COLLATERAL_TYPE_CODE_1 = put(COLLATERAL_TYPE_CODE, best32.);
run;







proc sql;
create table coll_sub_type_code_in as
select * from prcr.TB_IFRS9_COLLATERAL_TYPE_MAS
where COLLATERAL_SUB_TYPE_CODE in (select COLLATERAL_SUB_TYPE_CODE from temp_1);
quit;
/*IN ---- NOTE: Table WORK.TEST_1 created, with 224 rows and 5 columns.*/



proc sql;
create table coll_sub_type_code_notin as
select * from prcr.TB_IFRS9_COLLATERAL_TYPE_MAS
where COLLATERAL_SUB_TYPE_CODE not in (select COLLATERAL_SUB_TYPE_CODE from temp_1);
quit;
/*NOT IN---NOTE: Table WORK.TEST_1 created, with 661 rows and 5 columns. */




/*Step:-2*/



proc sql;
create table coll_type_code_in as
select * from prcr.TB_IFRS9_COLLATERAL_TYPE_MAS
where COLLATERAL_TYPE_CODE in (select COLLATERAL_TYPE_CODE_1 from temp_2);
quit;



/*NOTE: Table WORK.COLL_TYPE_CODE_IN created, with 0 rows and 5 columns.*/



proc sql;
create table coll_type_code_notin as
select * from prcr.TB_IFRS9_COLLATERAL_TYPE_MAS
where COLLATERAL_TYPE_CODE in (select COLLATERAL_TYPE_CODE_1 from temp_2);
quit;

thanks!

[JOL]

Have you tried converting the variable type and a full join?  It would help if you could send a sample of the data sources.

PROC SQL;
CREATE TABLE tablename AS
SELECT COLLATERAL_TYPE_CODE, INPUT(COLLATERAL_SUB_TYPE_CODE, COMMA20.) as COLLATERAL_SUB_TYPE_CODE
/* The input function is used to convert character to numeric */
FROM prcr.TB_IFRS9_COLLATERAL_TYPE_MAS as T1
FULL JOIN prcr.TB_IFRS9_COLLATERAL as t2

ON (t1.COLLATERAL_TYPE_CODE = t2.COLLATERAL_SUB_TYPE_CODE);
QUIT;