BookmarkSubscribeRSS Feed

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

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;

5 REPLIES 5
stat_sas
Ammonite | Level 13

group by calculated year month_date;

This doesn't seem to work, any reason as to why this might be?

SASKiwi
PROC Star

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! 


stat_sas
Ammonite | Level 13

Sorry, missed comma. Thanks SASKiwi.

group by calculated year, month_date;

Patrick
Opal | Level 21

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;

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
  • 5 replies
  • 554 views
  • 0 likes
  • 4 in conversation