Select Having Max

[KevinC_]

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

[yeshwanth]

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

[PGStats]

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...

PG

[Ksharp]

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