- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi, I have below tables that I want to join. Please help.
See attached "Q.xlsx". Table1, Table2 and Result table.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
What is the logic of the join?
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Tags:
- th
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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.
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Tags:
- ery clea
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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.
Paige Miller
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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: