Create a new column based on comparison

[salvavit]

Hi all, 

I need to create a new column in my existing table based on the comparison between another column of the same table and a column of another table.

My_table                                     Other_table

ID     Date                                   ID   

1       01/01/1975                        2

1       01/01/1990                        3

1       01/01/2010                        4

2       01/01/1998                        5

3       01/01/2011

3       01/01/1989

4       01/01/2020

5       01/01/2018

I need to add to My_table a new column named "Flag" that is "N" for all IDs that are present in the Other_table, and "Y" for the IDs that are not present in the other table, in correspondance of the oldest date. So in this case the final output would be:

My_table                                     

ID     Date                  Flag                   

1       01/01/1975          Y             

1       01/01/1990          N             

1       01/01/2010          N            

2       01/01/1998          N             

3       01/01/2011          N 

3       01/01/1989          N 

4       01/01/2020          N 

5       01/01/2018          N 

I have tried with both data step and proc sql but with no success. 

Can anyone help me with this?

Thank you in advance

[Reeza]

Can you please show us what you've tried so far? SQL with a join would be my approach. COALESCE() can check for missing values.

[novinosrin]

data My_table ;                                    
input ID     Date :$10.;
cards;   

1       01/01/1975                        2

1       01/01/1990                        3

1       01/01/2010                        4

2       01/01/1998                        5

3       01/01/2011

3       01/01/1989

4       01/01/2020

5       01/01/2018
;

data other_table;
 do id=2 to 5;
 output;
 end;
run;

data want;
  merge My_table(in=a) other_table(in=b);
  by id;
  flag='N';
  if not( a and b) and first.id then flag='Y';
run;

[salvavit]

Hello Novinosrin and thanks for your reply. Actually my IDs are not sequential, I wrote them like that for simplicity. Is your solution still possible to implement? So, to make my example closer to reality, my table is  like: 

ID             Date

823793    01/01/1975

823793    01/01/1990

823793    01/01/2010

367272    01/01/1998

389092    01/01/2011

991860    01/01/1989

991860    01/01/2020

[novinosrin · Accepted solution]

Hi @salvavit  As @Reeza  SQL is ideal

proc sql;
create table want as
select a.*,ifc(min(Date)=date and missing(b.id),'Y','N') as Flag
from my_table a left join other_table b
on a.id=b.id
group by a.id
order by a.id,date;
quit;

[mklangley]

Try this:

data my_table;
	input ID $ Date :ddmmyy10. ;
    format Date ddmmyy10.;
	datalines;
    1       01/01/1975
    1       01/01/1990
    1       01/01/2010
    2       01/01/1998
    3       01/01/2011
    3       01/01/1989
    4       01/01/2020
    5       01/01/2018
    ;
run;

data other_table;
    input ID $;
    datalines;
    2
    3
    4
    5
    ;
run;

/* determine the oldest date, by ID */
proc sql;
    create table oldest_date_by_id as
    select ID
          ,min(date) as oldest_date format ddmmyy10.
    from my_table
    group by ID
    ;
quit;

/* FLAG = Y if the record has the oldest date by ID, and where the ID is not in OTHER_TABLE; otherwise, FLAG = N */
proc sql;
    create table my_table_with_flag as
    select a.ID
          ,a.Date
          ,case
               when (a.Date = b.oldest_date and a.ID ne c.ID)
                   then 'Y'
               else 'N'
           end as flag
    from my_table a
    left join oldest_date_by_id b
        on a.ID = b.ID
        and a.Date = b.oldest_date
    left join other_table c
        on a.ID = c.ID
    ;
quit;