BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
helloSAS
Obsidian | Level 7

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

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
mklangley
Lapis Lazuli | Level 10

@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:
mklangley_0-1601994680592.png

 

View solution in original post

8 REPLIES 8
PaigeMiller
Diamond | Level 26

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
helloSAS
Obsidian | Level 7

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
Diamond | Level 26

What is the logic of the join?

--
Paige Miller
helloSAS
Obsidian | Level 7
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
Diamond | Level 26

@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
helloSAS
Obsidian | Level 7

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
Diamond | Level 26

@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
mklangley
Lapis Lazuli | Level 10

@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:
mklangley_0-1601994680592.png

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 951 views
  • 0 likes
  • 3 in conversation