SAS proc sql: select for each value the right interval

Question

Suppose there is a list of clients and each client has different contracts that have a due date. For each client, I want to select the contract with the first following duedate after a given reference date.

For example:

Table contracts:

Client	Contract	Due_date
Anthony	Contract A1	2023/03/05
Anthony	Contract A2	2024/03/05
Anthony	Contract A3	2025/03/05
Bart	Contract B1	2024/05/01
Bart	Contract B2	2024/08/01
Bart	Contract B3	2024/11/01
Bart	Contract B4	2024/05/01
Carla	Contract C1	2023/10/03
Carla	Contract C2	2024/10/03

Today we are 2024/08/19, so I would want to select for each client 1 contract that will be due firstly after today. So what I want is

Client	Contract
Anthony	Contract A3
Bart	Contract B3
Carla	Contract C2

What i would like to do is

proc sql;
create table DueContracts as (
select Client
       , contract 
from contracts
where due_date = min (due dates that are larger than *reference date*)
)
;quit;

The where clause is obviously pseude code, I don't know how to do this. I can do it for one seperate clients, but I don't know how to do it for all clients at once.

Answer 1

You are very close. You need to do pre-processing with where and post-processing with having. Think of it in two steps:

1. Get all due dates that are > the reference date (19AUG2024)
2. For each of those clients with due dates > 19AUG2024, filter to the date that is equal to the minimum of those dates

Here's how that looks:

proc sql;
    create table DueContracts as
        select Client, Contract 
        from contracts
        where due_date > '19AUG2024'd
        group by client
        having due_date = min(due_date)
    ;
quit;

Client  Contract
Anthony Contract A3
Bart    Contract B3
Carla   Contract C2