topic Re: how do i group interval by time and do calculation based on ID and class in SAS Programming

how do i group interval by time and do calculation based on ID and class

ihlayyel — Thu, 09 Nov 2017 12:04:05 GMT

TIME	X	Y	class	ID
16:00:06	103.785	71.45633	1	A
16:05:06	109.095	92.82449	2	A2
16:08:06	105.981	67.84456	1	A6
16:11:05	103.461	71.42452	3	F4
16:15:05	103.7674	71.47382	1	C55
16:17:26	107.4762	73.3445	3	E4
16:18:06	105.2773	87.58118	2	O8
16:19:21	103.7858	71.44293	2	H77
16:33:18	103.7201	71.37974	3	XX
16:40:11	107.3432	88.27521	3	X8
17:43:06	100.8496	67.38097	2	D
17:45:06	110.1006	66.26799	2	A
17:50:12	105.8542	87.77079	2	D4
17:55:09	96.14285	61.99789	2	F4
18:02:08	103.7829	71.48327	2	RR3
18:09:33	103.9275	71.49321	2	U7
18:25:12	104.7226	90.43502	3	S
18:30:05	109.6942	66.22699	3	S33
18:33:04	109.7128	97.24288	3	T17
18:40:44	56.12425	71.13522	3	G22
18:44:02	93.29675	63.89221	3	II1
18:50:13	109.7023	71.47756	1	S9

1- Above is a sample of a data frame which has thousands of records.

2- How can group it by the time (20 min) and class number. Then calculate the Euclidean distance between x, y using :

dist = sqrt( (x2 - x1)**2 + (y2 - y1)**2 )

3- An iteration of every 20 min for all data, no value duplicates in the single iteration. I have tried to implement this sample. but still not working.

The expected results for the 20min in two iteration: 

ID  CLASS  distnace 
A,A6    1   4.22695
A,C55   1   0.024806
A6, C55 1   4.251038
A2,O8   2   6.485861
A2,H77  2   22.030843
O8,H77  2   16.207033
F4, E4  3   4.4506
A,A6    1   17.279585

Re: how do i group interval by time and do calculation based on ID and class

Kurt_Bremser — Thu, 09 Nov 2017 12:25:07 GMT

Since SAS time (and datetime) values are counts of seconds, you can normalize them to 20-minute frames by doing

tim20 = int(time / 1200) * 1200;
format tim20 time8.;

and then use that in your by statement(s).

Re: how do i group interval by time and do calculation based on ID and class

PeterClemmensen — Thu, 09 Nov 2017 12:36:39 GMT

You can create groups based on intervals like this

data have;
input TIME:time8. X Y class ID$;
format time time8.;
datalines;
16:00:06 103.785 71.45633 1 A 
16:05:06 109.095 92.82449 2 A2 
16:08:06 105.981 67.84456 1 A6 
16:11:05 103.461 71.42452 3 F4 
16:15:05 103.7674 71.47382 1 C55 
16:17:26 107.4762 73.3445 3 E4 
16:18:06 105.2773 87.58118 2 O8 
16:19:21 103.7858 71.44293 2 H77 
16:33:18 103.7201 71.37974 3 XX 
16:40:11 107.3432 88.27521 3 X8 
17:43:06 100.8496 67.38097 2 D 
17:45:06 110.1006 66.26799 2 A 
17:50:12 105.8542 87.77079 2 D4 
17:55:09 96.14285 61.99789 2 F4 
18:02:08 103.7829 71.48327 2 RR3 
18:09:33 103.9275 71.49321 2 U7 
18:25:12 104.7226 90.43502 3 S 
18:30:05 109.6942 66.22699 3 S33 
18:33:04 109.7128 97.24288 3 T17 
18:40:44 56.12425 71.13522 3 G22 
18:44:02 93.29675 63.89221 3 II1 
18:50:13 109.7023 71.47756 1 S9 
;

data grouped;
	set have;
	interval= intck('minute20', '16:00:00't, time)+1;
run;

Regarding your desired output.. I don't understand why the Euclidian distance between A and A6 for Class 1 appears twice?

Re: how do i group interval by time and do calculation based on ID and class

ihlayyel — Thu, 09 Nov 2017 12:42:26 GMT

the output is for 2 iteration.kinldy look at the time itration 1 = 16:00:06 to 16:19:21 iteration2 = 16:33:18 to 16:40:11

Re: how do i group interval by time and do calculation based on ID and class

Ksharp — Thu, 09 Nov 2017 12:46:28 GMT

data have;
infile cards expandtabs;
input TIME $ 	X	Y	class	ID $;
cards;
16:00:06	103.785	71.45633	1	A
16:05:06	109.095	92.82449	2	A2
16:08:06	105.981	67.84456	1	A6
16:11:05	103.461	71.42452	3	F4
16:15:05	103.7674	71.47382	1	C55
16:17:26	107.4762	73.3445	3	E4
16:18:06	105.2773	87.58118	2	O8
16:19:21	103.7858	71.44293	2	H77
16:33:18	103.7201	71.37974	3	XX
16:40:11	107.3432	88.27521	3	X8
17:43:06	100.8496	67.38097	2	D
17:45:06	110.1006	66.26799	2	A
17:50:12	105.8542	87.77079	2	D4
17:55:09	96.14285	61.99789	2	F4
18:02:08	103.7829	71.48327	2	RR3
18:09:33	103.9275	71.49321	2	U7
18:25:12	104.7226	90.43502	3	S
18:30:05	109.6942	66.22699	3	S33
18:33:04	109.7128	97.24288	3	T17
18:40:44	56.12425	71.13522	3	G22
18:44:02	93.29675	63.89221	3	II1
18:50:13	109.7023	71.47756	1	S9
;
run;
proc sql;
create table want as
 select  catx(',',a.id,b.id) as id,Euclid(a.x-b.x,a.y-b.y) as distance
  from have as a,have as b
   where a.class=b.class and a.id ne b.id ;
quit;

Re: how do i group interval by time and do calculation based on ID and class

ihlayyel — Thu, 09 Nov 2017 12:54:56 GMT

That is nice, thank you so much. but have you consider the time interval?

Re: how do i group interval by time and do calculation based on ID and class

Ksharp — Thu, 09 Nov 2017 13:14:20 GMT

OK. No problem.

data have;
infile cards expandtabs;
input TIME : time8.	X	Y	class	ID $;
retain _time ;
format time _time time8.;
if _n_=1 then _time=time;
if time > _time+20*60 then do;group+1;_time=_time+20*60;end;
cards;
16:00:06	103.785	71.45633	1	A
16:05:06	109.095	92.82449	2	A2
16:08:06	105.981	67.84456	1	A6
16:11:05	103.461	71.42452	3	F4
16:15:05	103.7674	71.47382	1	C55
16:17:26	107.4762	73.3445	3	E4
16:18:06	105.2773	87.58118	2	O8
16:19:21	103.7858	71.44293	2	H77
16:33:18	103.7201	71.37974	3	XX
16:40:11	107.3432	88.27521	3	X8
17:43:06	100.8496	67.38097	2	D
17:45:06	110.1006	66.26799	2	A
17:50:12	105.8542	87.77079	2	D4
17:55:09	96.14285	61.99789	2	F4
18:02:08	103.7829	71.48327	2	RR3
18:09:33	103.9275	71.49321	2	U7
18:25:12	104.7226	90.43502	3	S
18:30:05	109.6942	66.22699	3	S33
18:33:04	109.7128	97.24288	3	T17
18:40:44	56.12425	71.13522	3	G22
18:44:02	93.29675	63.89221	3	II1
18:50:13	109.7023	71.47756	1	S9
;
run;
proc sql;
create table want as
 select  catx(',',a.id,b.id) as id,a.class as class,Euclid(a.x-b.x,a.y-b.y) as distance
  from have as a,have as b
   where a.class=b.class and a.group=b.group and a.id ne b.id ;
quit;

Re: how do i group interval by time and do calculation based on ID and class

ihlayyel — Thu, 09 Nov 2017 13:28:14 GMT

That is awesome. but iam facing a small issue here. iam reading data from csv file. so the code will be like that ?

data work.people;
if _n_=1 then _time=time;
if time > _time+20*60 then do;group+1;_time=_time+20*60;
end;
run;

proc sql;
create table want as
select catx(',',a.id,b.id) as id,a.class as class,Euclid(a.x-b.x,a.y-b.y) as distance
from have as a,have as b
where a.class=b.class and a.group=b.group and a.id ne b.id ;
quit;

Re: how do i group interval by time and do calculation based on ID and class

Ksharp — Thu, 09 Nov 2017 13:43:18 GMT

proc import datafile='c:\temp\x.csv' out=have dbms=csv replace;
guessingrows=32767;
run;

The code above could import csv file . and check if TIME variable is numeric variable and have TIME. format .

Re: how do i group interval by time and do calculation based on ID and class

Ksharp — Thu, 09 Nov 2017 13:45:41 GMT

Or use data step.

data have;
infile 'c:\temp\x.csv' truncover dsd;
input TIME : time8.	X	Y	class	ID : $40.;

Re: how do i group interval by time and do calculation based on ID and class

ihlayyel — Thu, 09 Nov 2017 13:55:57 GMT

I think you didn't get my point. I have import the data into sas. so when i call the data using this. it returns empty cell.

data work.people;
if _n_=1 then _time=time;
if time > _time+20*60 then do;group+1;_time=_time+20*60;
end;
run;

Re: how do i group interval by time and do calculation based on ID and class

Ksharp — Thu, 09 Nov 2017 14:02:11 GMT

OK. just use SET statement to replace INFILE . And remove INPUT.

Make sure time is numeric variable.

data have;
set have;

retain _time ;
format time _time time8.;
if _n_=1 then _time=time;
if time > _time+20*60 then do;group+1;_time=_time+20*60;end;
run;

Note: fixed

Re: how do i group interval by time and do calculation based on ID and class

ihlayyel — Thu, 09 Nov 2017 14:12:05 GMT

i have implemented that: I got this error
The time format is same as the sample I provided. Do i need to change it to other format?
ERROR: No DATALINES or INFILE statement.

Re: how do i group interval by time and do calculation based on ID and class

Ksharp — Thu, 09 Nov 2017 14:17:47 GMT

That is weird. Your table name is PEOPLE ? and it is in WORK libarary ?

data have;
set people  ;

retain _time ;
format time _time time8.;
if _n_=1 then _time=time;
if time > _time+20*60 then do;group+1;_time=_time+20*60;end;
run;

proc sql;
.............

Re: how do i group interval by time and do calculation based on ID and class

ihlayyel — Thu, 09 Nov 2017 15:00:47 GMT

Thank you so much. the code works perfectly.
when i run the code

this what I get.

78 where a.floor=b.floor and a.uid ne b.uid ;
ERROR: Insufficient space in file WORK.WANT.DATA.
ERROR: File WORK.WANT.DATA is damaged. I/O processing did not complete.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
79 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):

the total number of my data is 1048576 . Do you have any suggestion for that?
I really appreciate your help .
Thank you

Re: how do i group interval by time and do calculation based on ID and class

Kurt_Bremser — Thu, 09 Nov 2017 15:07:43 GMT

You're "simply" running out of space in the WORK library. First step: clean up yourself. Either remove unneeded datasets from WORK, or restart SAS altogether (that removes the WORK library physically).

If that does not remedy your problem, consider increasing available space in WORK, or at least use another library for your source and target datasets that resides on other storage.

PRC SQL does build a very large utility file while working, which might be the cause for your problem; since you get the error when writing the result dataset, at least building the utility file seems to have worked.

Re: how do i group interval by time and do calculation based on ID and class

ihlayyel — Fri, 10 Nov 2017 06:49:04 GMT

Hello, when i run the code the time function its not working. In data have>> it return all the groups 0 value.
if time > _time+20*60 then do;group+1;_time=_time+20*60;end;

could u please check that again ?

Re: how do i group interval by time and do calculation based on ID and class

ihlayyel — Fri, 10 Nov 2017 09:48:15 GMT

I have cleaned the work library and restart it again. but it dose not work. this is the error that i got.

NOTE: There were 1048575 observations read from the data set WORK.PEOPLE.

NOTE: The data set WORK.HAVE has 1048575 observations and 9 variables.

NOTE: DATA statement used (Total process time):

real time 1.18 seconds

cpu time 0.47 seconds

74 proc sql;

75 create table want as

76 select catx(',',a.uid,b.uid) as id, catx(',',a.floor,b.floor) as floor,Euclid(a.x-b.x,a.y-b.y) as distance

77 from have as a, have as b

78 where a.floor=b.floor and a.uid ne b.uid and a.group=b.group;

ERROR: Insufficient space in file WORK.WANT.DATA.

ERROR: File WORK.WANT.DATA is damaged. I/O processing did not complete.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

80 quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 22.38 seconds

cpu time 9.30 seconds

Re: how do i group interval by time and do calculation based on ID and class

Kurt_Bremser — Fri, 10 Nov 2017 10:01:00 GMT

Create a new library that is physically on another file system, and store your have and want datasets there; then only the intermediate file(s) automatically created by proc sql need space in WORK.

Or increase the size of the filesystem where WORK is located.

Re: how do i group interval by time and do calculation based on ID and class

Ksharp — Fri, 10 Nov 2017 12:37:14 GMT

Firstly , the code of mine is getting what you want?

If it was, then your original data is not like what you posted, can you post your real data and the output of

PROC CONTENTS DATA=PEOPLE;RUN: