Hi, I have below tables that I want to join. Please help.
See attached "Q.xlsx". Table1, Table2 and Result table.
@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:
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.
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
;
What is the logic of the join?
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.
@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.
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.
@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.
@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:
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.