My code below runs but i'm not getting the required output and would appreciate some helps.
The Month_date Count and Year is the output table but as you can see the values are the same for each year by month_date where they should differ. I have my CASE statement to help me do this but I cant seem to get it to work. I don't think i'm too far off but im new to this so unsure on the next steps.
OUTPUT
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 |
CODE
%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;
group by calculated year month_date;
This doesn't seem to work, any reason as to why this might be?
Did you put a comma in Stat's suggestion?
group by calculated year, month_date;
Also it would be very helpful to us if you are a little bit more precise in your answers. For example "it doesn't seem to work" could be "I get an error in my SAS log and the error is ......" or "the code runs without error but doesn't produce the expected output. This is what I am getting....and this is what I want.....". The bonus is you will get this fixed quicker!
Sorry, missed comma. Thanks SASKiwi.
group by calculated year, month_date;
Consider not using SAS Macro code at all but something as below:
data have;
format date date9.;
do date='01jan2004'd to '31dec2009'd;
output;
date=date+ceil(ranuni(0)*15);
end;
run;
proc format;
value year_range
low - '31Dec2004'd = 'upto2005'
'01Jan2008'd - high = '2008+'
other = [year.]
;
run;
proc sql;
create table want as
select
intnx('month',date,0,'b') as month_date format=monyy5.,
count(date) as count,
put(date,year_range.) as year
from have
group by calculated month_date, calculated year
;
quit;
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.