Hi Colleagues, Attached longitudinal data set shows how 5 bank accounts are swinging from one delinquency bucket to the other (original data set has over million records). SQL code provided at the very end of this returns the sum of variable 'balance' for each arrears band grouped by Current_date subject to this condition. Balances corresponding to NPNA Arrears_Band have to be summed up for only those accounts that have fallen into NPNA status after 28FEB2010. NPNA balances in 28FEB2010 and in all subsequent months of those accounts that were already in NPNA status in 28FEB2010 should be omitted from summing up. E.g. Account 1111111111's NPNA balance should be excluded from summing up in 28FEB2010 and in all subsequent months because it was already in NPNA status when we begin our modeling period in 28FEB2010. QUESTION: I need to incorporate another condition with regard to NPNA arrers bands. E.g. A/C 4444444444 has fallen into delq. bucket permanenelty in 28FEB2011. So, its NPNA balances in all subsequent months (in 31MAR2011 and beyond) should be omitted from summing up. Note that A/C 4444444444 has fallen into NPNA in 31DEC2010 too, but it was not permanenet, so 31DEC2010 NPNA balance should not be excluded. Likewise, in A/C 5555555555 NPNA balances in 31MAR2011 and beyond should be omitted from summing up. Final answer should be like this. Current_date current 1-30 30-60 60-90 90+ NPNA 28FEB2010 24 0 0 0 0 . 31MAR2010 25 23 0 0 0 . 30APR2010 25 0 23 0 0 . 31MAY2010 25 0 0 23 0 . 30JUN2010 0 0 0 0 23 . 31JUL2010 0 0 0 0 0 85 30NOV2010 25 0 0 0 0 . 31DEC2010 0 0 0 0 0 25 31JAN2011 25 18 0 0 0 . 28FEB2011 0 0 0 0 0 43 31MAR2011 0 0 0 0 0 . 30APR2011 0 0 0 0 0 . 31MAY2011 85 0 0 0 0 . Could anyone help me to modify this SQL code? Thanks, Mirisage proc sql; create table LOSS1 as select a1.*,a2.npna from( select Current_date , sum(case when Arrears_Band IN ('Current') then balance else 0 end ) as current, sum(case when Arrears_Band IN ('1 - 30') then balance else 0 end ) as One_to_30, sum(case when Arrears_Band IN ('30 - 60') then balance else 0 end ) as Thirty_to_60, sum(case when Arrears_Band IN ('60 - 90') then balance else 0 end ) as sixty_to_90, sum(case when Arrears_Band IN ('90 +') then balance else 0 end) as Ninety_plus from b where Product IN ('Personal OD','Personal Loan','Res. Mortgage') and Bank_number = 10 group by Current_date, Bank_number ) a1 left join ( select Current_date , sum(balance)as npna from b where Product IN ('Personal OD','Personal Loan','Res. Mortgage') and arrears_Band = "NPNA" and Bank_number = 10 and Account_number not in (select Account_number from b where current_date =mdy(02,28,2010) and arrears_Band = "NPNA") group by Current_date, Bank_number) a2 on a1.Current_date = a2.Current_date; quit;
... View more
