SAS Programming

DATA Step, Macro, Functions and more
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

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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