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