Hi I need some help !
basically i need table 1 and table 2 become table 3
table 1
key date desc1
012 1/1/2019 check
013 1/1/2019 nothing
013 9/1/2019 left
table 2
key date desc2
012 1/1/2019 up
012 9/1/2019 down
013 1/1/2019 right
table 3
key date desc1 desc2
012 1/1/2019 check up
012 9/1/2019 check down
013 1/1/2019 nothing right
013 9/1/2019 left right
For the data you posted, a simple MERGE does it:
data table1;
input key $ date :ddmmyy10. desc1 $;
format date yymmdd10.;
datalines;
012 1/1/2019 check
013 1/1/2019 nothing
013 9/1/2019 left
;
data table2;
input key $ date :ddmmyy10. desc2 $;
format date yymmdd10.;
datalines;
012 1/1/2019 up
012 9/1/2019 down
013 1/1/2019 right
;
data table3;
merge
table1
table2
;
by key;
run;
but this is only true until you run into a situation where there is more than one observation for a given key in both datasets; in this case, you will need a SQL full join, and some logic to deal with the dates, like
proc sql;
create table table3 as
select
coalesce(t1.key,t2.key) as key,
max(t1.date,t2.date) as date format=yymmdd10.,
t1.desc1,
t2.desc2
from
table1 t1
full join
table2 t2
on t1.key = t2.key
order by calculated key, calculated date
;
quit;
For the data you posted, a simple MERGE does it:
data table1;
input key $ date :ddmmyy10. desc1 $;
format date yymmdd10.;
datalines;
012 1/1/2019 check
013 1/1/2019 nothing
013 9/1/2019 left
;
data table2;
input key $ date :ddmmyy10. desc2 $;
format date yymmdd10.;
datalines;
012 1/1/2019 up
012 9/1/2019 down
013 1/1/2019 right
;
data table3;
merge
table1
table2
;
by key;
run;
but this is only true until you run into a situation where there is more than one observation for a given key in both datasets; in this case, you will need a SQL full join, and some logic to deal with the dates, like
proc sql;
create table table3 as
select
coalesce(t1.key,t2.key) as key,
max(t1.date,t2.date) as date format=yymmdd10.,
t1.desc1,
t2.desc2
from
table1 t1
full join
table2 t2
on t1.key = t2.key
order by calculated key, calculated date
;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.