topic Re: Select Having Max in SAS Procedures

Select Having Max

KevinC_ — Fri, 21 Jun 2013 21:09:13 GMT

Hello Everyone,

I would like to select the max(letter_date) from each customer and compare it to the due_date. For some reason the query return every row (not just the row with max letter_date). I have included sample data and my query. Any input would be greatly appreciated!

Thanks!

cust letter_date due_date

11111 5-29-2013 1-20-2013

11111 8-10-2011 1-20-2013

22222 4-28-2012 2-20-2013

33333 8-19-2012 1-12-2013

proc sql;

create table redefault as

select distinct a.cust, b.letter_date, a.due_date

from

tab1 a

join tab2 b on a.cust = b.cust

group by b.cust

having b.letter_date = max(b.letter_date) and

(max(b.letter_date) < a.due_date)

order by a.cust

;

quit

Re: Select Having Max

yeshwanth — Fri, 21 Jun 2013 21:32:20 GMT

Hello,

Try this

data y1;
input id d1 d2;
informat d1 mmddyy10.;
informat d2 mmddyy10.;
format d1 mmddyy10.;
format d2 mmddyy10.;
cards;
11111 5/29/2013 1/20/2013
11111 8/10/2011 1/20/2013
22222 4/28/2012 2/20/2013
33333 8/19/2012 1/12/2013
;
run;

proc sql;
create table y2 as
select id, max(d1), max(d2)
from y1
group by id
having max(d1)<max(d2);
quit;

Later you can change the dates to mmddyy10. format.

Hope this helps !!

Thankz

Yesh

Re: Select Having Max

PGStats — Fri, 21 Jun 2013 22:26:19 GMT

I think you need something like this:

data tab1;

input cust dueDate;

informat dueDate mmddyy10.;

format dueDate mmddyy10.;

datalines;

11111 1/20/2013

22222 2/20/2013

33333 1/12/2013

;

data tab2;

input cust letterDate;

informat letterDate mmddyy10.;

format letterDate mmddyy10.;

datalines;

11111 5/29/2013

11111 8/10/2011

22222 4/28/2012

33333 8/19/2012

;

proc sql;

create table lastLetter as

select

a.cust,

letterDate,

dueDate,

intck("MONTH", letterDate, dueDate, "CONTINUOUS") as monthsToGo

from

tab1 as a inner join

tab2 as b on a.cust=b.cust

group by a.cust

having letterDate=max(letterDate) and dueDate > max(letterDate);

quit;

proc print noobs; run;

MonthsToGo is optional...

Re: Select Having Max

Ksharp — Sat, 22 Jun 2013 01:44:46 GMT

In your SQL code, you mentioned two datasets, but you only post one, where is another one ?

data y1;
input id  d1  d2;
informat d1 mmddyy10.;
informat d2 mmddyy10.;
format d1 mmddyy10.;
format d2 mmddyy10.;
cards;
11111 5/29/2013 1/20/2013
11111 8/10/2011 1/20/2013
22222 4/28/2012 2/20/2013
33333 8/19/2012 1/12/2013
;
run;
proc sql;
 create table x as
  select *
   from y1
    where d1 lt d2 
      group by id
       having d1=max(d1);
quit;

Ksharp