topic Re: Finding the earliest date in PROC SQL in SAS Data Management

Finding the earliest date in PROC SQL

niam — Thu, 14 Feb 2019 16:23:23 GMT

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	.

Re: Finding the earliest date in PROC SQL

MarkWik — Thu, 14 Feb 2019 16:43:02 GMT

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

Re: Finding the earliest date in PROC SQL

niam — Thu, 14 Feb 2019 18:50:19 GMT

You are right. The four first observations should not be missing.

Re: Finding the earliest date in PROC SQL

novinosrin — Thu, 14 Feb 2019 21:18:51 GMT

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;