BookmarkSubscribeRSS Feed

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;

11 REPLIES 11
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

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;


RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

ballardw
Super User

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.

tbutkevich
Calcite | Level 5

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

Reeza
Super User

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

Malv72
Calcite | Level 5

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

Malv72
Calcite | Level 5

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-12Sep-12Oct-12Nov-12Dec-12Jan-13Feb-13Mar-13Apr-13
Up to 20055.46%5.00%5.56%5.98%5.86%5.71%5.26%5.08%5.15%
20055.77%4.95%5.01%5.84%5.10%5.47%5.61%4.57%5.06%
20066.78%5.97%6.05%6.24%6.36%6.47%6.17%6.32%6.71%
20075.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%
Overall5.97%5.87%5.88%6.06%5.97%5.90%5.64%5.47%5.77%
Value(£)Aug-12Sep-12Oct-12Nov-12Dec-12Jan-13Feb-13Mar-13Apr-13
Up to 200511.96%11.71%11.53%11.27%11.15%11.44%11.33%10.93%11.31%
200511.32%11.90%11.58%11.85%11.41%11.57%11.10%11.72%11.85%
200612.41%12.91%12.65%12.99%12.73%12.14%11.97%12.03%12.02%
200711.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%
Overall12.17%12.27%12.16%12.11%12.04%12.09%11.69%11.81%11.99%
ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1294 views
  • 0 likes
  • 6 in conversation