BookmarkSubscribeRSS Feed
KevinC_
Fluorite | Level 6

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


3 REPLIES 3
yeshwanth
Fluorite | Level 6

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
Opal | Level 21

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

PG
Ksharp
Super User

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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