Duplicate Rows After Left Join

jtennyson — Thu, 15 Feb 2018 14:09:14 GMT

I am trying to create a simple subtraction - I need to subtract one row of data from all the other rows. The best way I could think about doing this was to create a table segregating the line items I need to subtract and then join it to the table I need to subtract it from. When I join the tables it duplicates the line items (looks like it pulling the line items twice for each row.) I'm trying to get one row for each record. I have written CASE

This is the table I need to perform the subtraction on. Essentially if after the SENS2 there is an A subtract the TDBS line if it's a B subtract the TDSAS A line

Table to perform subtraction on:

scenario_desc TEST Total

DFAST2017_SENS2_AD_BBB10Y	TDSAS_A	1700587744.63
DFAST2017_SENS2_AD_GDPR	TDSAS_A	2524641512.12
DFAST2017_SENS2_AD_UR	TDSAS_A	1809725301.19
DFAST2017_SENS2_AU_BBB10Y	TDSAS_A	3054364731.83
DFAST2017_SENS2_AU_GDPR	TDSAS_A	2023551995.09
DFAST2017_SENS2_AU_UR	TDSAS_A	2866475522.15
DFAST2017_SENS2_BD_BBB10Y	TDBS	383260996.70
DFAST2017_SENS2_BD_GDPR	TDBS	397523601.08
DFAST2017_SENS2_BD_UR	TDBS	393753099.58
DFAST2017_SENS2_BU_BBB10Y	TDBS	409767402.79
DFAST2017_SENS2_BU_GDPR	TDBS	386205367.55
DFAST2017_SENS2_BU_UR	TDBS	404607525.42
DFAST2017_TDBS		383667322.80
DFAST2017_TDSAS_A		2297107152.97

I segregated the line items out in another table

scenario_desc TEST Total

DFAST2017_TDBS	TDBS	383667322.8
DFAST2017_TDSAS_A	TDSAS_A	2297107153.0

When I join them, I get duplicated line items

scenario_desc TEST Total Total2

DFAST2017_SENS2_AD_BBB10Y	TDSAS_A	1700587744.63	.
DFAST2017_SENS2_AD_BBB10Y	TDSAS_A	1700587744.63	2297107153.0
DFAST2017_SENS2_AD_GDPR	TDSAS_A	2524641512.12	.
DFAST2017_SENS2_AD_GDPR	TDSAS_A	2524641512.12	2297107153.0
DFAST2017_SENS2_AD_UR	TDSAS_A	1809725301.19	.
DFAST2017_SENS2_AD_UR	TDSAS_A	1809725301.19	2297107153.0
DFAST2017_SENS2_AU_BBB10Y	TDSAS_A	3054364731.83	.
DFAST2017_SENS2_AU_BBB10Y	TDSAS_A	3054364731.83	2297107153.0
DFAST2017_SENS2_AU_GDPR	TDSAS_A	2023551995.09	.
DFAST2017_SENS2_AU_GDPR	TDSAS_A	2023551995.09	2297107153.0
DFAST2017_SENS2_AU_UR	TDSAS_A	2866475522.15	.
DFAST2017_SENS2_AU_UR	TDSAS_A	2866475522.15	2297107153.0
DFAST2017_SENS2_BD_BBB10Y	TDBS	383260996.70	.
DFAST2017_SENS2_BD_BBB10Y	TDBS	383260996.70	383667322.8
DFAST2017_SENS2_BD_GDPR	TDBS	397523601.08	.
DFAST2017_SENS2_BD_GDPR	TDBS	397523601.08	383667322.8
DFAST2017_SENS2_BD_UR	TDBS	393753099.58	.
DFAST2017_SENS2_BD_UR	TDBS	393753099.58	383667322.8
DFAST2017_SENS2_BU_BBB10Y	TDBS	409767402.79	.
DFAST2017_SENS2_BU_BBB10Y	TDBS	409767402.79	383667322.8
DFAST2017_SENS2_BU_GDPR	TDBS	386205367.55	.
DFAST2017_SENS2_BU_GDPR	TDBS	386205367.55	383667322.8
DFAST2017_SENS2_BU_UR	TDBS	404607525.42	.
DFAST2017_SENS2_BU_UR	TDBS	404607525.42	383667322.8
DFAST2017_TDBS		383667322.80	.
DFAST2017_TDSAS_A		2297107152.97	.

PROC SQL;
CREATE TABLE PCL_Sub AS
SELECT DISTINCT
t1.scenario_desc,
CASE WHEN t1.scenario_desc CONTAINS ('_TDSAS') THEN 'TDSAS_A'
WHEN t1.scenario_desc CONTAINS ('_TDBS') THEN 'TDBS' END AS TEST,
SUM((t1.'1'n - t1.'0'n + t2.'1'n)
+(t1.'2'n - t1.'1'n + t2.'2'n)
+(t1.'3'n - t1.'2'n + t2.'3'n)
+(t1.'4'n - t1.'3'n + t2.'4'n)
+(t1.'5'n - t1.'4'n + t2.'5'n)
+(t1.'6'n - t1.'5'n + t2.'6'n)
+(t1.'7'n - t1.'6'n + t2.'7'n)
+(t1.'8'n - t1.'7'n + t2.'8'n)
+(t1.'9'n - t1.'8'n + t2.'9'n)) AS Total
FROM WORK.ALLL t1
LEFT JOIN WORK.CHARGE_OFF t2 ON (t1.scenario_desc = t2.scenario_desc)WHERE t1.scenario_desc CONTAINS ('_TDBS')
OR t1.scenario_desc CONTAINS ('_TDSAS_A')
GROUP BY t1.scenario_desc, t2.scenario_desc, TEST
;
RUN;
PROC SQL;
CREATE TABLE PCL AS
SELECT DISTINCT
t1.scenario_desc,
CASE WHEN t1.scenario_desc CONTAINS ('SENS2_A') THEN 'TDSAS_A'
WHEN t1.scenario_desc CONTAINS ('SENS2_B') THEN 'TDBS' END AS TEST,
(t1.'1'n - t1.'0'n + t2.'1'n)
+(t1.'2'n - t1.'1'n + t2.'2'n)
+(t1.'3'n - t1.'2'n + t2.'3'n)
+(t1.'4'n - t1.'3'n + t2.'4'n)
+(t1.'5'n - t1.'4'n + t2.'5'n)
+(t1.'6'n - t1.'5'n + t2.'6'n)
+(t1.'7'n - t1.'6'n + t2.'7'n)
+(t1.'8'n - t1.'7'n + t2.'8'n)
+(t1.'9'n - t1.'8'n + t2.'9'n)AS Total,
CASE WHEN Calculated test = t3.test THEN t3.total END AS Total2
FROM WORK.ALLL t1
LEFT JOIN WORK.CHARGE_OFF t2 ON (t1.scenario_desc = t2.scenario_desc)
LEFT JOIN WORK.PCL_Sub t3 ON TEST = t3.TEST

;
RUN;

Re: Duplicate Rows After Left Join

Mo_NHS — Thu, 15 Feb 2018 14:53:40 GMT

Add this WHERE clause to your last query:

where Total2 ne . ;

Re: Duplicate Rows After Left Join

jtennyson — Thu, 15 Feb 2018 15:04:04 GMT

Thanks so much! This worked perfectly!

topic Re: Duplicate Rows After Left Join in SAS Studio

Duplicate Rows After Left Join

Re: Duplicate Rows After Left Join

Re: Duplicate Rows After Left Join