topic Re: Case Statement in SAS Programming

Case Statement

anonymous_user — Thu, 14 Aug 2014 10:40:08 GMT

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;

Re: Case Statement

RW9 — Thu, 14 Aug 2014 10:49:33 GMT

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 */

Re: Case Statement

ballardw — Thu, 14 Aug 2014 15:04:41 GMT

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.

Re: Case Statement

tbutkevich — Thu, 14 Aug 2014 16:07:00 GMT

You need to add the case statement to the group by expression.

Re: Case Statement

Reeza — Thu, 14 Aug 2014 16:46:23 GMT

In addition to the other comments, you can't make date comparisons like that in SAS. It will compare alphabetically, so Apr2008 is < Jan2008.

Re: Case Statement

Malv72 — Thu, 14 Aug 2014 17:14:16 GMT

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

Re: Case Statement

anonymous_user — Fri, 15 Aug 2014 09:08:11 GMT

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;

Re: Case Statement

anonymous_user — Fri, 15 Aug 2014 09:25:13 GMT

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;

Re: Case Statement

RW9 — Fri, 15 Aug 2014 10:05:40 GMT

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.

Re: Case Statement

Malv72 — Fri, 15 Aug 2014 10:14:45 GMT

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;

Re: Case Statement

anonymous_user — Fri, 15 Aug 2014 10:56:53 GMT

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%

Re: Case Statement

ballardw — Fri, 15 Aug 2014 14:56:33 GMT

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.