Count Issue

Reply
Frequent Contributor
Posts: 140

Count Issue

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_datecountyear
Aug-1242882005
Aug-1242882006
Aug-1242882007
Aug-1242882008+
Aug-124288upto2005
Sep-1242622005
Sep-1242622006
Sep-1242622007
Sep-1242622008+
Sep-124262upto2005
Oct-1241232005
Oct-1241232006
Oct-1241232007
Oct-1241232008+
Oct-124123upto2005

%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;

Super User
Super User
Posts: 7,404

Re: Count Issue

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.

Trusted Advisor
Posts: 1,204

Re: Count Issue

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;

Ask a Question
Discussion stats
  • 2 replies
  • 162 views
  • 0 likes
  • 3 in conversation