BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jtennyson
Calcite | Level 5

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_BBB10YTDSAS_A1700587744.63
DFAST2017_SENS2_AD_GDPRTDSAS_A2524641512.12
DFAST2017_SENS2_AD_URTDSAS_A1809725301.19
DFAST2017_SENS2_AU_BBB10YTDSAS_A3054364731.83
DFAST2017_SENS2_AU_GDPRTDSAS_A2023551995.09
DFAST2017_SENS2_AU_URTDSAS_A2866475522.15
DFAST2017_SENS2_BD_BBB10YTDBS383260996.70
DFAST2017_SENS2_BD_GDPRTDBS397523601.08
DFAST2017_SENS2_BD_URTDBS393753099.58
DFAST2017_SENS2_BU_BBB10YTDBS409767402.79
DFAST2017_SENS2_BU_GDPRTDBS386205367.55
DFAST2017_SENS2_BU_URTDBS404607525.42
DFAST2017_TDBS 383667322.80
DFAST2017_TDSAS_A 2297107152.97
 

 

 

I segregated the line items out in another table

 

scenario_desc                         TEST              Total
DFAST2017_TDBSTDBS383667322.8
DFAST2017_TDSAS_ATDSAS_A2297107153.0
 

 

When I join them, I get duplicated line items

 

scenario_desc                                        TEST                    Total                    Total2
DFAST2017_SENS2_AD_BBB10YTDSAS_A1700587744.63.
DFAST2017_SENS2_AD_BBB10YTDSAS_A1700587744.632297107153.0
DFAST2017_SENS2_AD_GDPRTDSAS_A2524641512.12.
DFAST2017_SENS2_AD_GDPRTDSAS_A2524641512.122297107153.0
DFAST2017_SENS2_AD_URTDSAS_A1809725301.19.
DFAST2017_SENS2_AD_URTDSAS_A1809725301.192297107153.0
DFAST2017_SENS2_AU_BBB10YTDSAS_A3054364731.83.
DFAST2017_SENS2_AU_BBB10YTDSAS_A3054364731.832297107153.0
DFAST2017_SENS2_AU_GDPRTDSAS_A2023551995.09.
DFAST2017_SENS2_AU_GDPRTDSAS_A2023551995.092297107153.0
DFAST2017_SENS2_AU_URTDSAS_A2866475522.15.
DFAST2017_SENS2_AU_URTDSAS_A2866475522.152297107153.0
DFAST2017_SENS2_BD_BBB10YTDBS383260996.70.
DFAST2017_SENS2_BD_BBB10YTDBS383260996.70383667322.8
DFAST2017_SENS2_BD_GDPRTDBS397523601.08.
DFAST2017_SENS2_BD_GDPRTDBS397523601.08383667322.8
DFAST2017_SENS2_BD_URTDBS393753099.58.
DFAST2017_SENS2_BD_URTDBS393753099.58383667322.8
DFAST2017_SENS2_BU_BBB10YTDBS409767402.79.
DFAST2017_SENS2_BU_BBB10YTDBS409767402.79383667322.8
DFAST2017_SENS2_BU_GDPRTDBS386205367.55.
DFAST2017_SENS2_BU_GDPRTDBS386205367.55383667322.8
DFAST2017_SENS2_BU_URTDBS404607525.42.
DFAST2017_SENS2_BU_URTDBS404607525.42383667322.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;

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Mo_NHS
Fluorite | Level 6

Add  this WHERE clause to your last query: 

where Total2 ne . ;

View solution in original post

2 REPLIES 2
Mo_NHS
Fluorite | Level 6

Add  this WHERE clause to your last query: 

where Total2 ne . ;
jtennyson
Calcite | Level 5

Thanks so much!  This worked perfectly!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 6019 views
  • 0 likes
  • 2 in conversation