Proc Sql question

bg — Wed, 25 Nov 2009 22:36:06 GMT

I have a data set with an id variable and 2 datetime variables, arrival and seen.
For each id , I want to compute two variables as follows

fast <- sum((arrivalseenup))

slow <- sum((arrival>arrivalup) & (seen
The “up” variables are for the particular person, and the others are vectors with everyone’s data. The logical expressions evaluate to 1 if true and 0 if false. This is the way I could compute the two variables using R.

I'm an sql novice.
Will you give me suggestions for doing this using Sas proc Sql
thanks!

Re: Proc Sql question

LinusH — Thu, 26 Nov 2009 07:58:09 GMT

Since most persons in this forum is not R experts, I think you have do explain how you want the comparison between a single value and a vector should be done, preferable using some example data.

/Linus

Re: Proc Sql question

ChrisNZ — Thu, 26 Nov 2009 20:33:29 GMT

I don't know R, but could it be something like:
[pre]
select name
,age
,ifn(age>mean(age),1,0) as old
,ifn(age from sashelp.class;

Re: Proc Sql question

bg — Mon, 30 Nov 2009 18:11:51 GMT

Thanks everyone,
This is a sample data set.
For each id I have the time that the person arrived(arrive) and the time that the person was served(serve).

I would like to use Proc Sql to calculate for each person, the number of people who arrived before and were served after. Also the number of people who arrived after but were served before...

id arrive serve

0019267-813946128 01JAN08:10:29:00 01JAN08:11:20:35
0004015-113980951 09JAN08:10:32:52 09JAN08:11:02:29
0038173-013991677 10JAN08:16:56:00 10JAN08:17:07:24
0045494-013996145 11JAN08:14:40:00 11JAN08:14:59:29
0043376-414015842 16JAN08:14:17:00 16JAN08:14:40:15
0028129-814017478 16JAN08:16:10:43 16JAN08:16:28:39
0030854-414028337 18JAN08:14:54:00 18JAN08:15:05:09
0005823-814029965 19JAN08:10:54:00 19JAN08:11:35:41
0044188-114056579 27JAN08:18:06:23 27JAN08:18:11:58
0001847-314086274 01FEB08:14:17:00 01FEB08:14:32:01
0012913-514088602 03FEB08:11:41:00 03FEB08:12:30:24
0001847-314088736 04FEB08:03:28:00 04FEB08:03:36:56
0028637-114091619 04FEB08:11:39:00 04FEB08:11:50:32
0019478-614101647 05FEB08:22:25:00 05FEB08:22:52:32
0003291-314106568 06FEB08:15:16:31 06FEB08:15:25:48
0042351-314117061 08FEB08:13:47:13 08FEB08:14:44:36
0019496-414119198 09FEB08:05:29:00 09FEB08:06:06:26
0023793-114119481 09FEB08:20:25:00 09FEB08:20:40:08
0028129-814149048 15FEB08:15:07:00 15FEB08:15:12:44
0037508-014150410 16FEB08:11:55:00 16FEB08:12:07:33
0038173-014151040 18FEB08:14:07:08 18FEB08:15:09:01
0019691-614169857 21FEB08:17:29:23 21FEB08:18:32:46
0026138-614193554 27FEB08:14:23:08 27FEB08:14:52:50
0028844-614201490 28FEB08:21:19:00 28FEB08:21:38:13
0039265-114208518 02MAR08:10:14:00 02MAR08:10:31:06
0004179-314215358 03MAR08:23:14:00 03MAR08:23:25:09
0004115-714221678 04MAR08:17:08:52 04MAR08:17:13:55
0012179-714231727 06MAR08:14:27:47 06MAR08:14:43:50
0042722-514239453 08MAR08:07:40:46 08MAR08:07:46:40
0029663-514239719 09MAR08:13:44:56 09MAR08:14:15:36
thanks!

Re: Proc Sql question

Flip — Mon, 30 Nov 2009 19:04:35 GMT

Do you have any data where either of those conditions exist? Your question requires that people be present at the same time and I see no cases where that happens.

At any rate (and not testing this due to limited data)

proc sql;
create table fast as select a.id, count(*) as fast
from one a, one b where (b.arrive > a.arrive and b.serve < a.serve) group by a.id;
create table slow as select a.id, count(*) as slow
from one a, one b where (b.arrive < a.arrive and b.serve > a.serve) group by a.id;
create table times as select a.id, s.slow, f.fast
from one a left join slow s on a.id = s.id left join fast on a.id = f.id;
quit;

Should work.

Re: Proc Sql question

bg — Tue, 01 Dec 2009 00:20:19 GMT

Hi,
So sorry, I should have sorted the data before I copied a section of it to paste in the email. I've included a better data set below.
I tried the code and only had to add an f after "left join fast" (see below)and it seems to work....Thanks so much!

proc sql;
create table fast as select a.id, count(*) as fast
from one a, one b where (b.arrive > a.arrive and b.serve < a.serve) group by a.id;
create table slow as select a.id, count(*) as slow
from one a, one b where (b.arrive < a.arrive and b.serve > a.serve) group by a.id;
create table times as select a.id, s.slow, f.fast
from one a left join slow s on a.id = s.id left join fast f on a.id = f.id;
quit;

id arrive serve

4725726-313946246 01JAN08:00:03:55 01JAN08:00:10:14
4622227-013945046 01JAN08:00:22:00 01JAN08:00:34:43
2075755-813945047 01JAN08:00:51:00 01JAN08:01:00:13
0855146-413945184 01JAN08:01:33:00 01JAN08:01:48:01
4884596-713946075 01JAN08:02:13:51 01JAN08:02:36:53
4297056-513946078 01JAN08:03:11:00 01JAN08:03:19:32
4876975-613946079 01JAN08:03:19:00 01JAN08:03:35:23
4884598-313946081 01JAN08:03:26:29 01JAN08:03:41:14
4884600-913946083 01JAN08:04:00:00 01JAN08:04:07:12
0787902-413946087 01JAN08:05:20:00 01JAN08:05:29:25
4520860-513946088 01JAN08:05:28:00 01JAN08:05:38:46
3350473-013946090 01JAN08:05:51:00 01JAN08:06:02:50
4203254-913946091 01JAN08:06:12:00 01JAN08:06:23:12
0144179-513946093 01JAN08:07:18:31 01JAN08:07:31:35
0421372-613946094 01JAN08:07:27:00 01JAN08:07:42:41
4884603-313946095 01JAN08:07:44:49 01JAN08:07:54:19
4677696-813946096 01JAN08:07:48:00 01JAN08:08:06:22
4351012-613946101 01JAN08:08:33:13 01JAN08:08:42:17
4777448-913946100 01JAN08:08:34:00 01JAN08:08:57:42
4565367-613946103 01JAN08:08:46:00 01JAN08:08:56:50
4279797-913946105 01JAN08:08:49:28 01JAN08:09:08:35
4359722-113946107 01JAN08:08:58:00 01JAN08:09:24:36
4884605-013946108 01JAN08:09:05:00 01JAN08:09:16:23
3288758-913946111 01JAN08:09:12:12 01JAN08:09:25:11
4884606-813946110 01JAN08:09:13:00 01JAN08:09:34:24
4680208-013946114 01JAN08:09:23:00 01JAN08:09:38:27
0663964-013946115 01JAN08:09:35:00 01JAN08:09:51:24
4876482-713946118 01JAN08:09:54:00 01JAN08:10:02:18
4825135-813946119 01JAN08:09:58:36 01JAN08:10:07:15
4490649-013946122 01JAN08:10:10:00 01JAN08:10:25:12
4725726-313946246 01JAN08:00:03:55 01JAN08:00:10:14
4622227-013945046 01JAN08:00:22:00 01JAN08:00:34:43
2075755-813945047 01JAN08:00:51:00 01JAN08:01:00:13
0855146-413945184 01JAN08:01:33:00 01JAN08:01:48:01
4884596-713946075 01JAN08:02:13:51 01JAN08:02:36:53
4297056-513946078 01JAN08:03:11:00 01JAN08:03:19:32
4876975-613946079 01JAN08:03:19:00 01JAN08:03:35:23
4884598-313946081 01JAN08:03:26:29 01JAN08:03:41:14
4884600-913946083 01JAN08:04:00:00 01JAN08:04:07:12
0787902-413946087 01JAN08:05:20:00 01JAN08:05:29:25
4520860-513946088 01JAN08:05:28:00 01JAN08:05:38:46
3350473-013946090 01JAN08:05:51:00 01JAN08:06:02:50
4203254-913946091 01JAN08:06:12:00 01JAN08:06:23:12
0144179-513946093 01JAN08:07:18:31 01JAN08:07:31:35
0421372-613946094 01JAN08:07:27:00 01JAN08:07:42:41
4884603-313946095 01JAN08:07:44:49 01JAN08:07:54:19
4677696-813946096 01JAN08:07:48:00 01JAN08:08:06:22
4351012-613946101 01JAN08:08:33:13 01JAN08:08:42:17
4777448-913946100 01JAN08:08:34:00 01JAN08:08:57:42
4565367-613946103 01JAN08:08:46:00 01JAN08:08:56:50
4279797-913946105 01JAN08:08:49:28 01JAN08:09:08:35
4359722-113946107 01JAN08:08:58:00 01JAN08:09:24:36
4884605-013946108 01JAN08:09:05:00 01JAN08:09:16:23
3288758-913946111 01JAN08:09:12:12 01JAN08:09:25:11
4884606-813946110 01JAN08:09:13:00 01JAN08:09:34:24
4680208-013946114 01JAN08:09:23:00 01JAN08:09:38:27
0663964-013946115 01JAN08:09:35:00 01JAN08:09:51:24
4876482-713946118 01JAN08:09:54:00 01JAN08:10:02:18
4825135-813946119 01JAN08:09:58:36 01JAN08:10:07:15
4490649-013946122 01JAN08:10:10:00 01JAN08:10:25:12
4884607-613946124 01JAN08:10:12:00 01JAN08:10:19:04
0866310-613946125 01JAN08:10:18:00 01JAN08:10:34:59
4661229-913946126 01JAN08:10:25:00 01JAN08:10:59:54
0019267-813946128 01JAN08:10:29:00 01JAN08:11:20:35
4827079-413946129 01JAN08:10:36:00 01JAN08:11:11:47
4154234-913946131 01JAN08:10:48:23 01JAN08:11:45:18
4737996-213946133 01JAN08:11:02:00 01JAN08:11:51:00
1058544-913946135 01JAN08:11:12:00 01JAN08:11:29:58
4641326-113946138 01JAN08:11:34:00 01JAN08:12:09:59
4884608-413946139 01JAN08:11:38:00 01JAN08:12:01:55
4752952-213946140 01JAN08:11:38:00 01JAN08:12:28:16
4884609-213946142 01JAN08:11:47:36 01JAN08:12:46:37
4645163-513946144 01JAN08:12:03:00 01JAN08:12:20:44
3362277-513946148 01JAN08:12:34:00 01JAN08:14:29:45
0813433-213946149 01JAN08:12:38:02 01JAN08:12:54:33
2234992-913946151 01JAN08:12:38:53 01JAN08:14:33:43
0875966-913946152 01JAN08:12:40:33 01JAN08:14:38:44
0606278-413946153 01JAN08:12:48:07 01JAN08:13:19:53
4860669-513946154 01JAN08:12:53:44 01JAN08:13:05:51
0304176-013946156 01JAN08:12:56:36 01JAN08:13:51:41
4714331-413946157 01JAN08:13:14:00 01JAN08:14:39:19
4884590-813946158 01JAN08:13:15:00 01JAN08:15:01:12
1012111-613946161 01JAN08:13:22:38 01JAN08:14:27:53
4716264-513946159 01JAN08:13:24:25 01JAN08:13:36:10
4884612-213946162 01JAN08:13:27:06 01JAN08:14:53:14
4841255-613946163 01JAN08:13:41:00 01JAN08:14:07:46
4884614-913946165 01JAN08:13:46:47 01JAN08:15:06:25
0839187-413946166 01JAN08:13:56:00 01JAN08:15:26:19
4884615-713946170 01JAN08:14:03:49 01JAN08:15:34:54
4521333-113946172 01JAN08:14:18:00 01JAN08:14:47:24
4884618-113946174 01JAN08:14:22:45 01JAN08:15:37:42
4884619-013946176 01JAN08:14:28:35 01JAN08:15:55:58
3451938-213946175 01JAN08:14:30:01 01JAN08:15:41:54
4836368-713946177 01JAN08:14:32:20 01JAN08:15:52:24
0348897-713946178 01JAN08:14:37:17 01JAN08:16:02:24
1298641-613946180 01JAN08:14:48:21 01JAN08:15:22:21
4884620-313946182 01JAN08:14:53:25 01JAN08:16:24:25
4543692-613946183 01JAN08:15:00:46 01JAN08:16:16:04
4419623-913946184 01JAN08:15:08:00 01JAN08:16:23:26
4897596-813946186 01JAN08:15:14:57 01JAN08:15:26:54
4613283-113946189 01JAN08:15:30:00 01JAN08:16:27:49
4807423-513946190 01JAN08:15:35:25 01JAN08:16:35:44
4587456-713946192 01JAN08:15:40:50 01JAN08:17:13:38
4272466-113946194 01JAN08:15:45:00 01JAN08:17:24:59
4297060-313946195 01JAN08:15:47:57 01JAN08:16:50:13
4884622-013946199 01JAN08:15:51:40 01JAN08:16:16:10
4884621-113946197 01JAN08:15:54:22 01JAN08:17:59:06
4879547-113946198 01JAN08:16:06:01 01JAN08:17:44:49
3547655-513946202 01JAN08:16:08:40 01JAN08:17:33:17
4670869-513946206 01JAN08:16:11:31 01JAN08:18:10:04
1228841-713946207 01JAN08:16:12:54 01JAN08:18:22:14
4852752-313946203 01JAN08:16:16:00 01JAN08:16:34:00
4364673-713946208 01JAN08:16:29:30 01JAN08:16:52:27
4689777-313946210 01JAN08:16:40:29 01JAN08:16:59:45
4767521-913946211 01JAN08:16:43:00 01JAN08:18:41:45
4884625-413946213 01JAN08:17:03:47 01JAN08:18:31:10
3622221-213946218 01JAN08:17:50:32 01JAN08:19:15:29
0184023-113946220 01JAN08:18:25:47 01JAN08:18:51:58
4884629-713946221 01JAN08:18:34:00 01JAN08:19:00:49
0591538-413946223 01JAN08:19:56:00 01JAN08:20:07:01
0870664-613946224 01JAN08:20:12:00 01JAN08:20:21:55
4706299-313946226 01JAN08:20:47:00 01JAN08:20:55:57
3459167-913946227 01JAN08:20:55:00 01JAN08:21:03:29
4579320-613946228 01JAN08:21:03:00 01JAN08:21:23:21
4748022-113946229 01JAN08:21:09:00 01JAN08:21:33:30
0913488-313946230 01JAN08:21:10:00 01JAN08:21:47:21
2196241-413946233 01JAN08:21:36:00 01JAN08:21:52:53
1151290-913946234 01JAN08:21:39:00 01JAN08:21:54:24
4662063-113946235 01JAN08:21:40:00 01JAN08:21:57:52
2209714-813946238 01JAN08:22:16:00 01JAN08:22:27:24

topic Re: Proc Sql question in SAS Procedures

Proc Sql question

Re: Proc Sql question

Re: Proc Sql question

Re: Proc Sql question

Re: Proc Sql question

Re: Proc Sql question