topic Re: PROC SQL in SAS Programming

PROC SQL

anonymous_user — Sat, 16 Aug 2014 08:10:40 GMT

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;

Re: PROC SQL

stat_sas — Sat, 16 Aug 2014 11:44:28 GMT

group by calculated year month_date;

Re: PROC SQL

anonymous_user — Sun, 17 Aug 2014 19:33:41 GMT

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

Re: PROC SQL

SASKiwi — Sun, 17 Aug 2014 20:09:54 GMT

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!

Re: PROC SQL

Patrick — Sun, 17 Aug 2014 22:24:05 GMT

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;

Re: PROC SQL

stat_sas — Sun, 17 Aug 2014 22:40:24 GMT

Sorry, missed comma. Thanks SASKiwi.

group by calculated year, month_date;