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.
>Solution :
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;