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

[Kurt_Bremser]

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.

[ihlayyel]

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.

79

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

[Kurt_Bremser]

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.

[ihlayyel]

Thank you so much for your reply. I have created another library and move the files there. The work library is empty, but its not working.

[Kurt_Bremser]

---

@ihlayyel wrote:
Thank you so much for your reply. I have created another library and move the files there. The work library is empty, but its not working.

---

If you really moved your files to a different filesystem (and not just another directory on the same FS), then you need to increase space for your WORK.

Also run the cleanwork utility on your WORK location.

[ihlayyel]

Thank you for your reply, I have Re-Set the SAS Studio to clean up all the files. "still the same issue"
Just question, how can I increase the space of work library?
Or is it possible to change the work library to another library with a larger size?

[ihlayyel]

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 ?

[Ksharp]

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:

[ihlayyel]

Hello, yes your code is working on small size of the data. However, when i run it on all the dataset I get this issue. 

This is the output of the run

PROC CONTENTS DATA=PEOPLE;RUN:

 find the attachments for full results 

WORK.PEOPLE	1048575
DATA	7
V9	0
11/11/2017 14:40:26	56
11/11/2017 14:40:26	0
	NO
	NO
SOLARIS_X86_64, LINUX_X86_64, ALPHA_TRU64, LINUX_IA64
utf-8 Unicode (UTF-8)

[Ksharp]

Use another folder to replace WORK. 

libname x v9 'd:\temp\';

options user=x;

data want;

 set people;

.............

after running code, you can find WANT table in USER library.

[ihlayyel]

Thank you for your reply. 

Let me explain something. When I run the code separately,  i manage to generate table have successfully. When i want to do the grouping part using SQL i face the sapce issue.  

I have follow ur comment. But, when i run this code i got this error: 

libname x v9 'c:\temp\';
options user=x;
data want;
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;

---
proc sql;
create table want as
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
from have as a, have as b
where a.floor=b.floor and a.uid ne b.uid and a.group=b.group;
quit;

-----------

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

61

62 libname x v9 'c:\temp\';

NOTE: Library X does not exist.

63 options user=x;

____

8

ERROR 8-7: Library USER does not exist.

64

65 data want;

-----------------------

[Ksharp]

If your OS is linux or unix ,let OS administrator create a folder for you like :  /home/xxx/temp/ ,and make sure there is enough disk space.

libname x v9 ' /home/xxx/temp/ ';
options user=x;
data want;
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;

[ihlayyel]

Hi, it works and I got the results.

Thank you so much for your suggestion 🙂