Hello,
I am a bit new to scripting and I am trying to marge table "b" to table "a" using the nearest dates.
Table a:
NAME | DATE | SUMMERY |
TERRY | 4/23/2017 13:40 | 1 |
JHON | 4/23/2017 13:40 | 3 |
ADI | 4/24/2017 0:21 | 6 |
MIKE | 4/23/2017 13:40 | 0 |
ADI | 4/18/2017 0:21 | 1 |
TERRY | 4/18/2017 13:40 | 1 |
Table b:
DATE | NAME | Counter |
4/18/2017 7:36 | ADI | 135 |
4/18/2017 10:23 | JHON | 94 |
4/25/2017 0:00 | TERRY | 287 |
4/18/2017 7:39 | TERRY | 137 |
4/20/2017 15:24 | MIKE | 112 |
This is as far as I went, as you can see it is only merging the rows with the exact same date:
Clear Globals();
dt1 = Open( "\c:\1\a.jmp");
dt2 = Open( "\c:\1\b.jmp");
(dt1 << Join(
With( dt2 ),
By Matching Columns( : DATE = : DATE, :NAME = :NAME ),
Name( "Include non-matches" )(1, 0),
Preserve main table order( 1 ),
output table("Joined Table")
)
);
Can someone please guide me?
Thanks.
I recommend using the magical comma in proc sql.
PROC SQL;
create table differences as
select mydata.id, mydata.date, mydata.id as id2, mydata2.date as date2, mydata.date - mydata.date2 as difference
from mydata, mydata as mydata2;
Then simply find the record with the minimum difference for each mydata.id.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.