Hello,
I have a dataset that records the time that a message was sent to a user and their online activity. I want to know if online activity is a affected by messages. This is how my data look like:
Obs | UserID | TextDate | OnlineDate |
---|---|---|---|
2527 | 1109 | 11JAN19:19:00:03 | 11JAN19:18:12:47 |
2528 | 1109 | 12JAN19:11:00:05 | 11JAN19:18:12:47 |
2529 | 1109 | 12JAN19:17:00:04 | 11JAN19:18:12:47 |
2530 | 1109 | 12JAN19:19:00:05 | 11JAN19:18:12:47 |
2531 | 1109 | 13JAN19:11:00:04 | 11JAN19:18:12:47 |
2532 | 1109 | 14JAN19:13:00:12 | 14JAN19:11:38:58 |
2533 | 1109 | 15JAN19:09:00:06 | 14JAN19:20:36:40 |
2534 | 1109 | 15JAN19:13:00:21 | 14JAN19:20:36:40 |
2535 | 1109 | 15JAN19:15:00:14 | 14JAN19:20:36:40 |
2536 | 1109 | 15JAN19:19:00:16 | 14JAN19:20:36:40 |
2537 | 1109 | 16JAN19:13:00:06 | 16JAN19:09:43:33 |
2538 | 1109 | 16JAN19:15:00:17 | 16JAN19:09:43:33 |
2539 | 1109 | 16JAN19:17:00:10 | 16JAN19:09:43:33 |
2540 | 1109 | 17JAN19:11:00:49 | 16JAN19:09:43:33 |
2541 | 1109 | 18JAN19:09:00:05 | 16JAN19:09:43:33 |
2542 | 1109 | 18JAN19:11:00:05 | 16JAN19:09:43:33 |
2543 | 1109 | 19JAN19:09:00:03 | 18JAN19:11:45:43 |
2544 | 1109 | 20JAN19:11:00:12 | 19JAN19:11:15:08 |
2545 | 1109 | 20JAN19:13:00:12 | 19JAN19:11:15:08 |
2546 | 1109 | 20JAN19:17:00:19 | 19JAN19:11:15:08 |
2547 | 1109 | 21JAN19:11:00:20 | 19JAN19:11:15:08 |
2548 | 1109 | 21JAN19:13:00:18 | 19JAN19:11:15:08 |
2549 | 1109 | 21JAN19:15:00:23 | 19JAN19:11:15:08 |
2550 | 1109 | 21JAN19:19:00:14 | 19JAN19:11:15:08 |
2551 | 1109 | 22JAN19:13:00:12 | 22JAN19:11:14:43 |
2552 | 1109 | 23JAN19:09:00:16 | 22JAN19:17:08:25 |
2553 | 1109 | 23JAN19:11:00:15 | 22JAN19:17:08:25 |
2554 | 1109 | 23JAN19:15:00:17 | 22JAN19:17:08:25 |
I want to create an additional column, called ResponseDate that looks into the OnlineDate variable for each user and selects the earliest Onlinedate value after the TextDate. In this example, the new column, responseDate should be like this:
(This example is only for one user, I have muliple userIDs in my data)
Obs | UserID | TextDate | OnlineDate | ResponseDate |
---|---|---|---|---|
2527 | 1109 | 11JAN19:19:00:03 | 11JAN19:18:12:47 | . |
2528 | 1109 | 12JAN19:11:00:05 | 11JAN19:18:12:47 | . |
2529 | 1109 | 12JAN19:17:00:04 | 11JAN19:18:12:47 | . |
2530 | 1109 | 12JAN19:19:00:05 | 11JAN19:18:12:47 | . |
2531 | 1109 | 13JAN19:11:00:04 | 11JAN19:18:12:47 | 14JAN19:11:38:58 |
2532 | 1109 | 14JAN19:13:00:12 | 14JAN19:11:38:58 | 14JAN19:20:36:40 |
2533 | 1109 | 15JAN19:09:00:06 | 14JAN19:20:36:40 | 16JAN19:09:43:33 |
2534 | 1109 | 15JAN19:13:00:21 | 14JAN19:20:36:40 | 16JAN19:09:43:33 |
2535 | 1109 | 15JAN19:15:00:14 | 14JAN19:20:36:40 | 16JAN19:09:43:33 |
2536 | 1109 | 15JAN19:19:00:16 | 14JAN19:20:36:40 | 16JAN19:09:43:33 |
2537 | 1109 | 16JAN19:13:00:06 | 16JAN19:09:43:33 | 18JAN19:11:45:43 |
2538 | 1109 | 16JAN19:15:00:17 | 16JAN19:09:43:33 | 18JAN19:11:45:43 |
2539 | 1109 | 16JAN19:17:00:10 | 16JAN19:09:43:33 | 18JAN19:11:45:43 |
2540 | 1109 | 17JAN19:11:00:49 | 16JAN19:09:43:33 | 18JAN19:11:45:43 |
2541 | 1109 | 18JAN19:09:00:05 | 16JAN19:09:43:33 | 18JAN19:11:45:43 |
2540 | 1109 | 18JAN19:11:00:05 | 16JAN19:09:43:33 | 18JAN19:11:45:43 |
2543 | 1109 | 19JAN19:09:00:03 | 18JAN19:11:45:43 | 19JAN19:11:15:08 |
2544 | 1109 | 20JAN19:11:00:12 | 19JAN19:11:15:08 | 22JAN19:11:14:43 |
2545 | 1109 | 20JAN19:13:00:12 | 19JAN19:11:15:08 | 22JAN19:11:14:43 |
2546 | 1109 | 20JAN19:17:00:19 | 19JAN19:11:15:08 | 22JAN19:11:14:43 |
2547 | 1109 | 21JAN19:11:00:20 | 19JAN19:11:15:08 | 22JAN19:11:14:43 |
2548 | 1109 | 21JAN19:13:00:18 | 19JAN19:11:15:08 | 22JAN19:11:14:43 |
2549 | 1109 | 21JAN19:15:00:23 | 19JAN19:11:15:08 | 22JAN19:11:14:43 |
2550 | 1109 | 21JAN19:19:00:14 | 19JAN19:11:15:08 | 22JAN19:11:14:43 |
2551 | 1109 | 22JAN19:13:00:12 | 22JAN19:11:14:43 | 22JAN19:17:08:25 |
2552 | 1109 | 23JAN19:09:00:16 | 22JAN19:17:08:25 | . |
2553 | 1109 | 23JAN19:11:00:15 | 22JAN19:17:08:25 | . |
2554 | 1109 | 23JAN19:15:00:17 | 22JAN19:17:08:25 | . |
data have;
input Obs UserID (TextDate OnlineDate) (:datetime20.);
format TextDate OnlineDate datetime20.;
drop obs;
cards;
2527 1109 11JAN19:19:00:03 11JAN19:18:12:47
2528 1109 12JAN19:11:00:05 11JAN19:18:12:47
2529 1109 12JAN19:17:00:04 11JAN19:18:12:47
2530 1109 12JAN19:19:00:05 11JAN19:18:12:47
2531 1109 13JAN19:11:00:04 11JAN19:18:12:47
2532 1109 14JAN19:13:00:12 14JAN19:11:38:58
2533 1109 15JAN19:09:00:06 14JAN19:20:36:40
2534 1109 15JAN19:13:00:21 14JAN19:20:36:40
2535 1109 15JAN19:15:00:14 14JAN19:20:36:40
2536 1109 15JAN19:19:00:16 14JAN19:20:36:40
2537 1109 16JAN19:13:00:06 16JAN19:09:43:33
2538 1109 16JAN19:15:00:17 16JAN19:09:43:33
2539 1109 16JAN19:17:00:10 16JAN19:09:43:33
2540 1109 17JAN19:11:00:49 16JAN19:09:43:33
2541 1109 18JAN19:09:00:05 16JAN19:09:43:33
2542 1109 18JAN19:11:00:05 16JAN19:09:43:33
2543 1109 19JAN19:09:00:03 18JAN19:11:45:43
2544 1109 20JAN19:11:00:12 19JAN19:11:15:08
2545 1109 20JAN19:13:00:12 19JAN19:11:15:08
2546 1109 20JAN19:17:00:19 19JAN19:11:15:08
2547 1109 21JAN19:11:00:20 19JAN19:11:15:08
2548 1109 21JAN19:13:00:18 19JAN19:11:15:08
2549 1109 21JAN19:15:00:23 19JAN19:11:15:08
2550 1109 21JAN19:19:00:14 19JAN19:11:15:08
2551 1109 22JAN19:13:00:12 22JAN19:11:14:43
2552 1109 23JAN19:09:00:16 22JAN19:17:08:25
2553 1109 23JAN19:11:00:15 22JAN19:17:08:25
2554 1109 23JAN19:15:00:17 22JAN19:17:08:25
;
proc sql;
create table want as
select a.*, min(b.OnlineDate) as response_Date format datetime20.
from have a left join have b
on a.UserID=a.UserID and b.OnlineDate>a.TextDate
group by a.userid,a.textdate,a.onlinedate
order by a.userid, a.textdate,a.onlinedate;
quit;
why the first 4 of responsedate are missing?
should those be filled with the next >datetime value of onlinedate
for example,
2527 | 1109 | 11JAN19:19:00:03 | 11JAN19:18:12:47 | 14JAN19:11:38:58 |
You are right. The four first observations should not be missing.
data have;
input Obs UserID (TextDate OnlineDate) (:datetime20.);
format TextDate OnlineDate datetime20.;
drop obs;
cards;
2527 1109 11JAN19:19:00:03 11JAN19:18:12:47
2528 1109 12JAN19:11:00:05 11JAN19:18:12:47
2529 1109 12JAN19:17:00:04 11JAN19:18:12:47
2530 1109 12JAN19:19:00:05 11JAN19:18:12:47
2531 1109 13JAN19:11:00:04 11JAN19:18:12:47
2532 1109 14JAN19:13:00:12 14JAN19:11:38:58
2533 1109 15JAN19:09:00:06 14JAN19:20:36:40
2534 1109 15JAN19:13:00:21 14JAN19:20:36:40
2535 1109 15JAN19:15:00:14 14JAN19:20:36:40
2536 1109 15JAN19:19:00:16 14JAN19:20:36:40
2537 1109 16JAN19:13:00:06 16JAN19:09:43:33
2538 1109 16JAN19:15:00:17 16JAN19:09:43:33
2539 1109 16JAN19:17:00:10 16JAN19:09:43:33
2540 1109 17JAN19:11:00:49 16JAN19:09:43:33
2541 1109 18JAN19:09:00:05 16JAN19:09:43:33
2542 1109 18JAN19:11:00:05 16JAN19:09:43:33
2543 1109 19JAN19:09:00:03 18JAN19:11:45:43
2544 1109 20JAN19:11:00:12 19JAN19:11:15:08
2545 1109 20JAN19:13:00:12 19JAN19:11:15:08
2546 1109 20JAN19:17:00:19 19JAN19:11:15:08
2547 1109 21JAN19:11:00:20 19JAN19:11:15:08
2548 1109 21JAN19:13:00:18 19JAN19:11:15:08
2549 1109 21JAN19:15:00:23 19JAN19:11:15:08
2550 1109 21JAN19:19:00:14 19JAN19:11:15:08
2551 1109 22JAN19:13:00:12 22JAN19:11:14:43
2552 1109 23JAN19:09:00:16 22JAN19:17:08:25
2553 1109 23JAN19:11:00:15 22JAN19:17:08:25
2554 1109 23JAN19:15:00:17 22JAN19:17:08:25
;
proc sql;
create table want as
select a.*, min(b.OnlineDate) as response_Date format datetime20.
from have a left join have b
on a.UserID=a.UserID and b.OnlineDate>a.TextDate
group by a.userid,a.textdate,a.onlinedate
order by a.userid, a.textdate,a.onlinedate;
quit;
Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.
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.