Select rows with specific conditions

[flik]

Hi,

I am very new with SAS. I have a dataset like this:

ID_num     Start_Date        End_Date           Other_cols

001           01/01/2018       05/03/2018              ....

001           05/03/2018       10/08/2018              ....

001           10/08/2018       12/11/2018              ....

001           12/11/2018       11/01/2019              ....

002           05/04/2018       07/07/2018              ....

002           07/07/2018       20/12/2019              ....

003           01/05/2018       17/09/2018              ....

003           17/09/2018       17/03/2019             ....

003           17/03/2019       20/05/2019              ....

004           19/08/2019       28/09/2019             ....

004           28/09/2019       30/12/2019              ....

I would like to extract all the rows of ID having the max(end_date) = 31/05/2019, so with end_date(ID) <= 31/05/2019. My output should have all rows of ID 001 and ID 003. Could you help me?

Thanks

[andreas_lds · Accepted solution]

Can be solved with group by and having in proc sql:

proc sql;
   create table want as
   select *
      from have
         group by d_Num
            having max(End_Date) <= '31May2019'd
   ;
quit;