Proc SQL not joining properly-CodePudding

This is my code:

proc sql;
create table firstjoin as
select a.*
from want a
left join want3 b
on a.date=b.mindate and a.id=b.id;
quit;

Where want is a table with many rows and dates per ID and want3 is a table with just one row and date per ID.

Want looks something like:

Date       | ID | Company | Click 
----------- ---- --------- --------
01/01/2021 | 01 | Us      | 1
01/01/2021 | 01 | Us      | 1
01/01/2021 | 01 | Other   | 1
02/01/2021 | 01 | Us      | 0 
02/01/2021 | 01 | Other   | 0
02/01/2021 | 02 | Us      | 1 
03/01/2021 | 02 | Us      | 1 
03/01/2021 | 02 | Us      | 0

And want3 looks like:

MinDate    | ID 
----------- ----
01/01/2021 | 01 
02/01/2021 | 02

What I want to do is match both tables so only the first date rows are shown for each ID. That date is the one in want3. The result should look like:

Date       | ID | Company | Click 
----------- ---- --------- --------
01/01/2021 | 01 | Us      | 1
01/01/2021 | 01 | Us      | 1
01/01/2021 | 01 | Other   | 1
02/01/2021 | 02 | Us      | 1

For some reason, when I run this join, the result looks like want i.e. there are still many rows and dates per ID.

CodePudding user response：

Have you tryed innerjoin


proc sql;
create table firstjoin as
select a.*
from want a
inner join want3 b
on a.date=b.mindate and a.id=b.id;
quit;