Hi, Can anyone help with my case statement please as i cant get it to work?
proc sql;
create table work.kptarrears_vintagevalue as
select distinct month_date,
count(*) as count,
sum(balance_outstanding) as balance,
CASE WHEN MONTH_DATE => 'Jan08' THEN '2008+'
when =< 'dec04' then 'upto2005'
when between Jan06 and dec06 then 2006
else '2007' end as Year
from gbasel.baseljul14
where optimum_platform = 'Optimum'
and arrears_multiplier >= 3
and Arrears_lit_stage_code not in ('L4','L5','L6')
group by month_date;
quit;
Yes, don't do conditionals within groupings. Also try to think about process rather than overall e.g:
What does my output table need. Where does the data come from. What do I need to do on it. Then break your problem up so you can see the pieces:
proc sql;
create table WORK.KPTARREARS_VINTAGEVALUE as
select *,
case when MONTH_DATE => 'Jan08' THEN '2008+'
when =< 'dec04' then 'upto2005'
when between Jan06 and dec06 then 2006
else '2007' end as YEAR
from (
select distinct
MONTH_DATE,
count(*) as COUNT,
sum(BALANCE_OUTSTANDING) as BALANCE,
from (
select *
from GBASEL.BASELJUL14
where OPTIMUM_PLATFORM = 'Optimum'
and ARREARS_MULTIPLIER >= 3
and ARREARS_LIT_STAGE_CODE not in ('L4','L5','L6')
)
group by MONTH_DATE
);
quit;
Now this may not be the best way of coding it, however you can copy out the subqueries into proc sql's of their own and see what data is going in, manipulations etc. Makes it easier to debug. Note I am selecting data based on a where in a subquery. This is then grouped to get sums and counts. This data is then passed into the final select which does the case on the column and decides on the output.
/* Note not tested as no test data/required output provided */
My code runs now as it didnt before but 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? I have tried this in the groupinds but my SQL wont run so im really unsure as to what i need to do.
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;
Ive tried this and it wont work either -
proc sql;
create table work.kptarrears_vintagevalue as
select *,
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 (
select distinct
month_date,
count(*) as count,
sum(BALANCE_OUTSTANDING) as BALANCE
from (
select *
from gbasel.baseljul14
where optimum_platform = 'Optimum'
and arrears_multiplier >= 3
and Arrears_lit_stage_code not in ('L4','L5','L6')
)
group by month_date, completion
);
quit;
Well straight off the bat, in your outer select clause you use a variable completion which is not in the inner select hence doesn't exist. Can you provide some test data and what you want the output to look like as I don't have data to run this on.
Also this code
CASE WHEN MONTH_DATE => 'Jan08' THEN '2008+'
when =< 'dec04' then 'upto2005'
when between Jan06 and dec06 then 2006
else '2007' end as Year
you change from treating Month_date as character ('Jan08') to what SAS will likely try to treat as a variable Jan06 instead of a value 'Jan06' besides needing to explicitly include Month_date in each when clause if that is what you are testing/comparing.
I suspect that you had at least one error message and you should include them when code doesn't work.
You need to add the case statement to the group by expression.
In addition to the other comments, you can't make date comparisons like that in SAS. It will compare alphabetically, so Apr2008 is < Jan2008.
As Reeza mentioned you are using a text value co compare against. if MONTH_DATE is a date variable then you could try the case statement below.
CASE
WHEN MONTH_DATE >= '01Jan2008'd THEN '2008+'
when MONTH_DATE <= '31Dec2004'd then '2005 or before'
when MONTH_DATE between '01Jan2006'd and '31Dec2006'd then '2006'
else '2007' end as Year
The SQL looks la little over complicated but without fully understanding what you need to do would the below work for you? I have changes upto2005 into 2004- incase you need to order the data for any reason. As RW9 said if you supply some test data and what you are expecting to see then that would be good.
proc sql;
create table work.kptarrears_vintagevalue as
select
case
when month_date < '01jan2005'd then '2004-'
when month_date between '01Jan2005'd and '31dec2005'd then '2005 '
when month_date between '01Jan2006'd and '31dec2006'd then '2006 '
when month_date between '01Jan2007'd and '31dec2007'd then '2007 '
else '2008+' end as year,
count(*) as count,
sum(BALANCE_OUTSTANDING) as BALANCE
from gbasel.baseljul14
where optimum_platform = 'Optimum'
and arrears_multiplier >= 3
and Arrears_lit_stage_code not in ('L4','L5','L6')
group by 1
;
quit;
Im trying to achive this (below)-
If I can make the numbers split by year and then convert into a %
Volume (#) | Aug-12 | Sep-12 | Oct-12 | Nov-12 | Dec-12 | Jan-13 | Feb-13 | Mar-13 | Apr-13 |
Up to 2005 | 5.46% | 5.00% | 5.56% | 5.98% | 5.86% | 5.71% | 5.26% | 5.08% | 5.15% |
2005 | 5.77% | 4.95% | 5.01% | 5.84% | 5.10% | 5.47% | 5.61% | 4.57% | 5.06% |
2006 | 6.78% | 5.97% | 6.05% | 6.24% | 6.36% | 6.47% | 6.17% | 6.32% | 6.71% |
2007 | 5.83% | 5.62% | 5.67% | 5.72% | 5.50% | 5.64% | 5.27% | 4.50% | 4.83% |
2008+ | 6.87% | 6.29% | 6.03% | 6.31% | 6.92% | 5.94% | 6.36% | 5.61% | 6.35% |
Overall | 5.97% | 5.87% | 5.88% | 6.06% | 5.97% | 5.90% | 5.64% | 5.47% | 5.77% |
Value(£) | Aug-12 | Sep-12 | Oct-12 | Nov-12 | Dec-12 | Jan-13 | Feb-13 | Mar-13 | Apr-13 |
Up to 2005 | 11.96% | 11.71% | 11.53% | 11.27% | 11.15% | 11.44% | 11.33% | 10.93% | 11.31% |
2005 | 11.32% | 11.90% | 11.58% | 11.85% | 11.41% | 11.57% | 11.10% | 11.72% | 11.85% |
2006 | 12.41% | 12.91% | 12.65% | 12.99% | 12.73% | 12.14% | 11.97% | 12.03% | 12.02% |
2007 | 11.27% | 12.07% | 12.14% | 11.34% | 11.85% | 11.49% | 11.48% | 11.47% | 11.04% |
2008+ | 12.76% | 12.77% | 13.11% | 12.80% | 12.95% | 12.31% | 11.79% | 12.17% | 12.22% |
Overall | 12.17% | 12.27% | 12.16% | 12.11% | 12.04% | 12.09% | 11.69% | 11.81% | 11.99% |
You don't need a case statement to recode the values you can use a format:
proc format;
picture daterecode
low - '31DEC2005'd = 'Up to 2005' (datatype=date)
'01JAN2006'd - '31DEC2007'd = %Y (datatype=date)
high = '2008+' (datatype=date)
;
run;
data temp;
x= '15AUG2004'd;
y = '12JUN2006'd;
z = '10Feb2009'd;
run;
proc print data=temp;
format x y z daterecode.;
run;
You can use your date value and assign the format for use in the report generator.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.