topic Re: Join tables in SAS Programming

Join tables

helloSAS — Tue, 06 Oct 2020 13:22:24 GMT

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

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

Re: Join tables

PaigeMiller — Tue, 06 Oct 2020 13:27:53 GMT

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.

Re: Join tables

helloSAS — Tue, 06 Oct 2020 13:36:13 GMT

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 04JAN2020:08:06:03

11111 06JAN2020: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 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

;

Re: Join tables

PaigeMiller — Tue, 06 Oct 2020 13:38:34 GMT

What is the logic of the join?

Re: Join tables

helloSAS — Tue, 06 Oct 2020 13:50:31 GMT

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.

Re: Join tables

PaigeMiller — Tue, 06 Oct 2020 13:58:56 GMT

@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.

Re: Join tables

helloSAS — Tue, 06 Oct 2020 14:06:47 GMT

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.

Re: Join tables

mklangley — Tue, 06 Oct 2020 14:31:39 GMT

@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:

Re: Join tables

PaigeMiller — Tue, 06 Oct 2020 14:33:05 GMT

@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.