topic Re: Join all from both tables in SAS Programming

Join all from both tables

someone_new — Wed, 07 Oct 2020 12:57:39 GMT

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

Re: Join all from both tables

Cynthia_sas — Wed, 07 Oct 2020 13:43:11 GMT

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

Re: Join all from both tables

Kurt_Bremser — Wed, 07 Oct 2020 13:49:34 GMT

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;