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!
... View more
