🔒 This topic is solved and locked.
Need further help from the community? Please
sign in and ask a new question.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 02-14-2019 11:23 AM
(8110 views)
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 | . |
- Tags:
- proc sql
1 ACCEPTED SOLUTION
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
3 REPLIES 3
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You are right. The four first observations should not be missing.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;