Join tables

[helloSAS]

Hi, I have below tables that I want to join. Please help.

See attached "Q.xlsx". Table1, Table2 and Result table.

[PaigeMiller]

Join by what variable(s)?

Most of us will not download Excel files, they are security risk. Better you should provide the data as SAS data step code. 

[helloSAS]

sure.

let me know if this works.

data table1;

infile datalines delimiter=',';

input id dts_tab1 datetime20.;

datalines;

11111 03JAN2020:08:06:03

11111 05JAN2020:16:06:03

11111 07JAN2020:08:06:03

;

data table2;

infile datalines delimiter=',';

input id dts_tab2 datetime20.;

datalines;

11111 01JAN2020:08:06:03

11111 01JAN2020:08:06:03

11111 04JAN2020:08:06:03

11111 04JAN2020:08:06:03

11111 04JAN2020:08:06:03

11111 06JAN2020:08:06:03

11111 08JAN2020:08:06:03

11111 08JAN2020:08:06:03

;

data result;

infile datalines delimiter=',';

input id dts_tab2 dts_tab1 datetime20.;

datalines;

11111 01JAN2020:08:06:03 .

11111 01JAN2020:08:06:03 .

11111 04JAN2020:08:06:03 03JAN2020:08:06:03

11111 04JAN2020:08:06:03 03JAN2020:08:06:03

11111 04JAN2020:08:06:03 03JAN2020:08:06:03

11111 06JAN2020:08:06:03 05JAN2020:16:06:03

11111 08JAN2020:08:06:03 07JAN2020:08:06:03

11111 08JAN2020:08:06:03 07JAN2020:08:06:03

;

[PaigeMiller]

What is the logic of the join?

[helloSAS]

Spoiler

I want to join both the tables on ID variable. In the result table I want dts_tab2 where dts_tab1 is > dts_tab2. but I don't want to a many to many join. dts_tab2 should be the latest occurrence.

[PaigeMiller]

---

@helloSAS wrote:

I want to join both the tables on ID variable. In the result table I want dts_tab2 where dts_tab1 is > dts_tab2. but I don't want to a many to many join. dts_tab2 should be the latest occurrence.

---

In your result table, you do not have dts_tab1>dts_tab2. 

[helloSAS]

sorry it should be dts_tab2 > dts_tab1 without many to many join and always capture latest occurrence of dts_tab1. for ex: 6th row in result table I want dts_tab1 as 05JAN.. and not 03JAN...

Hope this helps.

[PaigeMiller]

---

@helloSAS wrote:

 for ex: 6th row in result table I want dts_tab1 as 05JAN.. and not 03JAN...

---

Show me the desired table as adjusted by this explanation.

[mklangley · Accepted solution]

@helloSAS  Is this what you're looking for?

data table1;
    infile datalines;
    input id dts_tab1 :datetime20.;
    format dts_tab1 datetime20.;
    datalines;
    11111 03JAN2020:08:06:03
    11111 05JAN2020:16:06:03
    11111 07JAN2020:08:06:03
    ;
run;

data table2;
    infile datalines;
    input id dts_tab2 :datetime20.;
    format dts_tab2 datetime20.;
    datalines;
    11111 01JAN2020:08:06:03
    11111 01JAN2020:08:06:03
    11111 04JAN2020:08:06:03
    11111 04JAN2020:08:06:03
    11111 04JAN2020:08:06:03
    11111 06JAN2020:08:06:03
    11111 08JAN2020:08:06:03
    11111 08JAN2020:08:06:03
    ;
run;

data table2_nobs;
    set table2;
    n = _n_;
run;

proc sql;
    create table work.joined as
    select a.id
          ,a.n
          ,a.dts_tab2 format datetime20.
          ,b.dts_tab1 format datetime20.
    from table2_nobs a
    left join table1 b
        on a.id = b.id
        and a.dts_tab2 > b.dts_tab1
    order by n, dts_tab1;
quit;

data work.want (drop=n);
    set joined;
    by n dts_tab1;
    if last.n;
run;

Result: