Count Issue

[#1]

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;

[RW9]

Hi,

Please refer to the responses in the other post: https://communities.sas.com/thread/60348

Your SQL code will not work as given.  Create your grouping variables in a sub-query, with the where clause in that sub-query.  Then use the resulting data in your grouping clause.

[stat_sas]

Hi,

I think a slight change in the syntax will fix it. In group by clause just add year

group by calculated year month_date;