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
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;
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
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.