PROC SQL

Reply
anonymous_user
Posts: 0

PROC SQL

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;

Trusted Advisor
Posts: 1,228

Re: PROC SQL

group by calculated year month_date;

anonymous_user
Posts: 0

Re: PROC SQL

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

Super User
Posts: 3,233

Re: PROC SQL

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! 


Trusted Advisor
Posts: 1,228

Re: PROC SQL

Sorry, missed comma. Thanks SASKiwi.

group by calculated year, month_date;

Respected Advisor
Posts: 4,132

Re: PROC SQL

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;

Ask a Question
Discussion stats
  • 5 replies
  • 248 views
  • 0 likes
  • 4 in conversation