topic Re: Joining timesliced tables in SAS Data Management

Joining timesliced tables

Filipvdr — Fri, 27 Sep 2019 12:14:43 GMT

Hello guys..

I'm joining two timesliced tables but i'm struggling to get the correct output

Table A:

ID NAME FKEY FROM TO PRODUCT_DESC

1 BASKET 2 1/1/2010 1/1/2015 BASKETBALL

1 BASKET 2 2/1/2015 31/12/9999 BASKETBALL SPALDING

2 PRODUCT2 3 1/1/2010 31/12/9999 TEMP

TABLE B (FKEY is ID here)

FKEY FROM TO PRODUCT_CAT_DESCR

2 1/1/2010 31/12/2013 SPORTS EQUIPMENT

2 1/1/2014 31/12/9999 BASKETBALL EQUIPMENT

I'm joining on the FKEY.

As a result I would like to have 4 records for ID 1 with 4 different from/to's.

Any idea's?

Re: Joining timesliced tables

ballardw — Fri, 27 Sep 2019 15:11:42 GMT

@Filipvdr wrote:

Hello guys..

I'm joining two timesliced tables but i'm struggling to get the correct output

Table A:

ID NAME FKEY FROM TO PRODUCT_DESC

1 BASKET 2 1/1/2010 1/1/2015 BASKETBALL

1 BASKET 2 2/1/2015 31/12/9999 BASKETBALL SPALDING

2 PRODUCT2 3 1/1/2010 31/12/9999 TEMP

TABLE B (FKEY is ID here)

FKEY FROM TO PRODUCT_CAT_DESCR

2 1/1/2010 31/12/2013 SPORTS EQUIPMENT

2 1/1/2014 31/12/9999 BASKETBALL EQUIPMENT

I'm joining on the FKEY.

As a result I would like to have 4 records for ID 1 with 4 different from/to's.

Any idea's?

Can you show the code you attempted?

And what you actually expect the output to look like for that example data? It is not at all clear what values might be needed in the result for NAME, PRODUCT_DESC and PRODUCT_CAT_DESCR in the results.

And verify that the From and To are actually SAS date values and not character?

Re: Joining timesliced tables

ErikLund_Jensen — Fri, 27 Sep 2019 15:22:49 GMT

Hi @Filipvdr

First - Please supply test data as code to create the data sets, so we know what we are dealing with and doesn't have to bother with actual values of date variables etc. and can work on a solution instead.

Second - Will any given ID in table A have more than one FKEY, and/or more than one NAME, and could there be overlapping intervals for any given ID?

I have an effecient macro to do the trick, but I would like to test it on your data before I post it, so I can get the macro options right according to your answers. Problem is the macro documentation is written in danish, so it will probably need some explanation too. I have some spare time this weekend....

Re: Joining timesliced tables

Filipvdr — Mon, 30 Sep 2019 08:57:18 GMT

data tableA;
format from to date9. name PRODUCT_DESC $20.;
id = 1;
name = "BASKET";
FKEY = 2;
FROM = "01JAN2010"d;
TO = "01JAN2015"d;
PRODUCT_DESC = "BASKETBALL";
output;
id = 1;
name = "BASKET";
FKEY = 2;
FROM = "02JAN2015"d;
TO = "31DEC2099"d;
PRODUCT_DESC = "BASKETBALL SPALDING";
output;
id = 2;
name = "PRODUCT2";
FKEY = 3;
FROM = "01JAN2010"d;
TO = "31DEC2099"d;
PRODUCT_DESC = "TEMP";
output;
run;

data tableB;
format fromb tob date9.  PRODUCT_CAT_DESCR $20.;
fkey = 2;
fromb = "01JAN2010"d;
tob = "31DEC2013"d;
PRODUCT_CAT_DESCR = "SPORTS EQUIPMENT";
output;
fkey = 2;
fromb = "01JAN2014"d;
tob = "31DEC2099"d;
PRODUCT_CAT_DESCR = "BASKETBALL EQUIPMENT";
output;
run;

proc sql;
 create table records as select * from tablea,tableb where tablea.fkey=tableb.fkey
AND	(	tableb.fromb	BETWEEN	tablea.from AND tablea.to	
AND		tableb.tob	BETWEEN	tablea.from AND tablea.to	); 
quit;

Here is some code to generate the data. My last step to create a table records is trying to link both tables together. The output is only one row while my wanted output should be 4 rows, each for one timeslice which is available in the data.

so the product basket now should have 3 rows, one for each timeslice

Re: Joining timesliced tables

Filipvdr — Mon, 30 Sep 2019 11:48:16 GMT

I found this paper and i will try it out: https://pdfs.semanticscholar.org/dc07/8531d26794c3ca8a5079c8abc451eef4eedd.pdf

Re: Joining timesliced tables

ErikLund_Jensen — Thu, 03 Oct 2019 15:48:56 GMT

Hi @Filipvdr

I had a look at Jim Moon's paper, and I notice that it relies heavily on the between-operator. It is a good approch to interval-joining small tables, but it is extremely slow when working with large tables, especially DBMS-tables.

The macro I mentioned in my previous post (attached) is developed to overcome that and work fast with big tables. We use it in many production jobs, where interval joins with between used to run up to 2 hours, and now run in as many minutes instead.

Given your test input, this is the call:

%fletrensinterval(
	tableA, 
	tableB, 
	tableOUT, 
	unita=FKEY, datefirsta=FROM, datelasta=TO, 
	unitb=FKEY, datefirstb=fromb, datelastb=tob, 
	join=left, 
	byvarA=ID);

TableA and TableB are the input tables, and tableOUT the resulting output tables.

unita, datefirsta and datelasta are the variables holding the key to join on and the from- and to-dates i TableA.

unitb, datefirstb and datelastb are the variables holding the key to join on and the from- and to-dates i TableB.

Join=left preserves all keys/intervals in TableA, even if the key is not present in TableB, or (part of) an interval in TableA is outside corresponding intervals in TableB

ByvarA=ID is the name of a grouping variable in TableA (in this case ID), so separate joins are made for each ID value i A, if the join key (in this case FKEY) exists with more than one ID in TableA

Result: