topic Re: Create a new column based on comparison in New SAS User

Create a new column based on comparison

salvavit — Mon, 08 Jun 2020 15:58:35 GMT

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

Re: Create a new column based on comparison

Reeza — Mon, 08 Jun 2020 16:24:25 GMT

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.

Re: Create a new column based on comparison

novinosrin — Mon, 08 Jun 2020 16:25:59 GMT


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;

Re: Create a new column based on comparison

salvavit — Mon, 08 Jun 2020 16:38:13 GMT

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

Re: Create a new column based on comparison

novinosrin — Mon, 08 Jun 2020 16:44:54 GMT

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;

Re: Create a new column based on comparison

mklangley — Mon, 08 Jun 2020 16:48:53 GMT

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;