## max(date) specific for each contract

Solved
Occasional Contributor
Posts: 12

# max(date) specific for each contract

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

thanks!

Accepted Solutions
Solution
‎11-25-2016 03:39 AM
Super User
Posts: 9,599

## Re: max(date) specific for each contract

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.

All Replies
Solution
‎11-25-2016 03:39 AM
Super User
Posts: 9,599

## Re: max(date) specific for each contract

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.

Occasional Contributor
Posts: 12

## Re: max(date) specific for each contract

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

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

Super User
Posts: 9,599

## Re: max(date) specific for each contract

Yep, did you try my code above?

Occasional Contributor
Posts: 12

## Re: max(date) specific for each contract

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

☑ This topic is solved.