Join all from both tables

[someone_new]

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

[Cynthia_sas]

Hi:
Take a look at the sample code here: https://communities.sas.com/t5/SAS-Programming/Merging-Tables/td-p/580772 on the 8/17/2019 posting. It shows 5 of the possible combinations from 2 datasets being merged. Your example seems very similar.
Cynthia

[Kurt_Bremser · Accepted solution]

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;