max(date) specific for each contract

[pkonopnicki]

Hi guys,

I have a question. I want to pull out distinct customer_id and certain (expiration) date from a table that contains history of expiration dates for certain bonuses. 

PROC SQL;
create table work.datt as
select distinct t1.CONT_ID, max(t2.EXPIRE_DATE) FORMAT=DATETIME20. from CONTRACTS t1
inner join CONTRACT_HIST t2 on t2.CONT_ID = t1.CONT_ID;
QUIT;

But then it takes one, general max date and pulls out contracts only for this one, specific date. Same happens when I'm using function 'having date =max(date)'.

Could you please help out how to make it max(date) but separate for each customer?

thanks!

[RW9 · Accepted solution]

Yes, you haven't supplied any group by statement.  Simplest method is to put your join in a subquery then the returned data group by CONT_ID:

proc sql;
  create table WORK.DATT as
  select  distinct 
          CONT_ID, 
          max(EXPIRE_DATE) format=datetime20. 
  from    (select * 
           from CONTRACTS T1
           inner join CONTRACT_HIST T2 
           on T2.CONT_ID=T1.CONT_ID) 
  group   by CONT_ID;
quit;

Obviously I can't test this as you haven't provided any test data.

[pkonopnicki]

meaning for example i have:

id	date
1	2016-01-01
1	2015-01-01
2	2018-03-01
3	2013-01-01
3	2015-01-01
4	2016-01-01
4	2017-01-01
4	2018-10-01

I want to have:

id	date
1	2016-01-01
2	2018-03-01
3	2015-01-01
4	2018-10-01

what I received:

id	date
1	2018-10-01
2	2018-10-01
3	2018-10-01
4	2018-10-01

[RW9]

Yep, did you try my code above?

[pkonopnicki]

ok I was dumb for few moments there, group by cont_id solved it. thanks a lot

😉