Im having issues with my report in that the count is the same value for each year (example below), any idea why it doesnt break it down and split it as per the case statement?
| month_date | count | year |
| Aug-12 | 4288 | 2005 |
| Aug-12 | 4288 | 2006 |
| Aug-12 | 4288 | 2007 |
| Aug-12 | 4288 | 2008+ |
| Aug-12 | 4288 | upto2005 |
| Sep-12 | 4262 | 2005 |
| Sep-12 | 4262 | 2006 |
| Sep-12 | 4262 | 2007 |
| Sep-12 | 4262 | 2008+ |
| Sep-12 | 4262 | upto2005 |
| Oct-12 | 4123 | 2005 |
| Oct-12 | 4123 | 2006 |
| Oct-12 | 4123 | 2007 |
| Oct-12 | 4123 | 2008+ |
| Oct-12 | 4123 | upto2005 |
%macro vintage_value(A1,A2);
proc sql;
create table work.kptarrears_vintagevalue_&A1. as
select distinct month_date,
count(*) as count,
case when completion < '01jan2005'd then 'upto2005'
when completion between '01Jan2005'd and '31dec2005'd then '2005'
when completion between '01Jan2006'd and '31dec2006'd then '2006'
when completion between '01Jan2007'd and '31dec2007'd then '2007'
else '2008+' end as year
from gbasel.basel&A2.
where optimum_platform = 'Optimum'
and arrears_multiplier >= 3
and Arrears_lit_stage_code not in ('L4','L5','L6')
group by month_date;
quit;
proc sort data = work.kptarrears_vintagevalue_&A1.;
by month_date;
run;
%mend vintage_value;
%vintage_value(201407,jul14);
%vintage_value(201406,jun14);
%vintage_value(201405,may14);
%vintage_value(201404,apr14);
%vintage_value(201403,mar14);
%vintage_value(201402,feb14);
%vintage_value(201401,jan14);
%vintage_value(201312,dec13);
%vintage_value(201311,nov13);
%vintage_value(201310,oct13);
%vintage_value(201309,sep13);
%vintage_value(201308,aug13);
%vintage_value(201307,jul13);
%vintage_value(201306,jun13);
%vintage_value(201305,may13);
%vintage_value(201304,apr13);
%vintage_value(201303,mar13);
%vintage_value(201302,feb13);
%vintage_value(201301,jan13);
%vintage_value(201212,dec12);
%vintage_value(201211,nov12);
%vintage_value(201210,oct12);
%vintage_value(201209,sep12);
%vintage_value(201208,aug12);
data allvintage_value;
set
kptarrears_vintagevalue_201208 kptarrears_vintagevalue_201209 kptarrears_vintagevalue_201210
kptarrears_vintagevalue_201211 kptarrears_vintagevalue_201212 kptarrears_vintagevalue_201301 kptarrears_vintagevalue_201302 kptarrears_vintagevalue_201303 kptarrears_vintagevalue_201304 kptarrears_vintagevalue_201305 kptarrears_vintagevalue_201306 kptarrears_vintagevalue_201307
kptarrears_vintagevalue_201308 kptarrears_vintagevalue_201309 kptarrears_vintagevalue_201310 kptarrears_vintagevalue_201311 kptarrears_vintagevalue_201312 kptarrears_vintagevalue_201401
kptarrears_vintagevalue_201402 kptarrears_vintagevalue_201403 kptarrears_vintagevalue_201404 kptarrears_vintagevalue_201405 kptarrears_vintagevalue_201406 kptarrears_vintagevalue_201407;
run;
