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!
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: