## link different date with same ID

Occasional Contributor
Posts: 16

# link different date with same ID

Hi,

Example: Person 001 (in reality, many persons IDs) log into personal account online, then visit different websites, the data are in separate tables

001            2010-01-01 08:00:01AM

001            2010-01-01 10:00:01AM

001            2010-01-02  09:00:00AM

001            2010-01-03  09:00:01AM

(2) Web A data

001            2010-01-01 08:01:00AM

001            2010-01-01 08:30:00AM

001            2010-01-01 08:45:00AM

001            2010-01-01 10:10:10AM

001            2010-01-02  09:10:00AM

001            2010-01-02  10:00:03AM

001            2010-01-02   11:00:10AM

001            2010-01-03   11:30:00AM

001            2010-01-03   11:33:00AM

001            2010-01-03   12:30:00AM

(3) Web B data

001            2010-01-01 08:06:00AM

001            2010-01-01 08:39:00AM

001            2010-01-01 08:47:00AM

001            2010-01-01 11:10:10AM

001            2010-01-02  09:30:00AM

001            2010-01-02  10:20:03AM

001            2010-01-02   11:20:10AM

001            2010-01-03   11:33:00AM

001            2010-01-03   11:56:00AM

001            2010-01-03   12:33:00AM

001            2010-01-03   11:34:00AM

001            2010-01-03   11:36:00AM

001            2010-01-03   12:55:00AM

How could I link those data based on person ID and log in account time, please note there are more than one visits on each web sites after log in.

Thanks!

Super User
Posts: 23,307

## Re: link different date with same ID

What are you looking for as output? Based on the data sets provided, please show what you would expect as output and explain the logic, then we can help you with what type of approach would be required.

@joe66 wrote:

Hi,

Example: Person 001 (in reality, many persons IDs) log into personal account online, then visit different websites, the data are in separate tables

001            2010-01-01 08:00:01AM

001            2010-01-01 10:00:01AM

001            2010-01-02  09:00:00AM

001            2010-01-03  09:00:01AM

(2) Web A data

001            2010-01-01 08:01:00AM

001            2010-01-01 08:30:00AM

001            2010-01-01 08:45:00AM

001            2010-01-01 10:10:10AM

001            2010-01-02  09:10:00AM

001            2010-01-02  10:00:03AM

001            2010-01-02   11:00:10AM

001            2010-01-03   11:30:00AM

001            2010-01-03   11:33:00AM

001            2010-01-03   12:30:00AM

(3) Web B data

001            2010-01-01 08:06:00AM

001            2010-01-01 08:39:00AM

001            2010-01-01 08:47:00AM

001            2010-01-01 11:10:10AM

001            2010-01-02  09:30:00AM

001            2010-01-02  10:20:03AM

001            2010-01-02   11:20:10AM

001            2010-01-03   11:33:00AM

001            2010-01-03   11:56:00AM

001            2010-01-03   12:33:00AM

001            2010-01-03   11:34:00AM

001            2010-01-03   11:36:00AM

001            2010-01-03   12:55:00AM

How could I link those data based on person ID and log in account time, please note there are more than one visits on each web sites after log in.

Thanks!

Posts: 5,482

## Re: link different date with same ID

You could concatenate the website tables, adding a web site id, or do this separately for each table, this way:

``````data login;
input ID time &:anydtdtm32.;
format time datetime19.;
datalines;
001 2010-01-01 08:00:01AM
001 2010-01-01 10:00:01AM
001 2010-01-02 09:00:00AM
001 2010-01-03 09:00:01AM
;

data web_a;
input ID time &:anydtdtm32.;
format time datetime19.;
datalines;
001 2010-01-01 08:01:00AM
001 2010-01-01 08:30:00AM
001 2010-01-01 08:45:00AM
001 2010-01-01 10:10:10AM
001 2010-01-02 09:10:00AM
001 2010-01-02 10:00:03AM
001 2010-01-02 11:00:10AM
001 2010-01-03 11:30:00AM
001 2010-01-03 11:33:00AM
001 2010-01-03 12:30:00AM
;

proc sql;
create table web_a_login as
select
a.*,
from web_a as a left join
login as b on a.id=b.id and b.time <= a.time
group by a.id, a.time
having b.time=max(b.time);
select * from web_a_login;
quit;``````
PG
Posts: 5,482

## Re: link different date with same ID

Actually, this would be more efficient than my previous post:

``````data web_a_login;
by id time;