divide rows into groups

[Vendy]

Hi,

I need to create some cycle or something else in SAS proc-sql which divide data into groups.

I have data:

ID1	ID2	TIME
1234	12	22MAY2015:16:10:00.000
1234	12	22MAY2015:16:15:00.000
1234	12	12JUN2015:6:35:00.000
1234	12	12JUN2015:16:35:00.000
6549	45	15APR2015:16:10:00.000
6549	45	18APR2015:13:15:00.000
6549	45	18APR2015:13:18:00.000
6549	12	22MAY2015:14:15:00.000
6549	12	22MAY2015:14:20:00.000

and I need create new column GROUP where will be the same id for those rows which have same ID1, same ID2 and difference between TIME is max 10 minutes.

Result will be:

ID1	ID2	TIME	GROUP
1234	12	22MAY2015:16:10:00.000	1
1234	12	22MAY2015:16:15:00.000	1
1234	12	12JUN2015:6:35:00.000	2
1234	12	12JUN2015:16:35:00.000	3
6549	45	15APR2015:16:10:00.000	4
6549	45	18APR2015:13:15:00.000	5
6549	45	18APR2015:13:18:00.000	5
6549	12	22MAY2015:14:15:00.000	6
6549	12	22MAY2015:14:20:00.000	6

Thank you.

[LinusH]

Job for the data step, not SQL.

Use retain/log() together with BY and first./last. logic.

[Vendy]

Unfortunatelly I am new in SAS so data step I know only a little.

And it is possible that one group will have more than two rows.

[Steelers_In_DC · Accepted solution]

You should verify the output but I think this will get you the correct solution:

data have;

infile cards dsd;

informat id1 id2 8. time datetime.;

format id1 id2 8. time datetime.;

input ID1 ID2 TIME;

cards;

1234,12,22MAY2015:16:10:00.000

1234,12,22MAY2015:16:15:00.000

1234,12,12JUN2015:6:35:00.000

1234,12,12JUN2015:16:35:00.000

6549,45,15APR2015:16:10:00.000

6549,45,18APR2015:13:15:00.000

6549,45,18APR2015:13:18:00.000

6549,12,22MAY2015:14:15:00.000

6549,12,22MAY2015:14:20:00.000

;

run;

proc sort data=have;by id1 id2 time;

data want(drop=_:);

set have;

by id1 id2 time;

format _l_time datetime.;

_l_time = lag(time);

if not first.id1 and not first.id2 then do;

_diff_time = (time - _l_time)/60;

end;

if first.id2 or _diff_time > 10 then group + 1;

run;

[Vendy]

Thank you very much. It works.

[Ksharp]

Code: Program

data have;
infile cards truncover expandtabs;
input id1 id2 time : anydtdtm30.;
format time datetime.;
cards;
1234 12 22MAY2015:16:10:00.000 
1234 12 22MAY2015:16:15:00.000 
1234 12 12JUN2015:6:35:00.000  
1234 12 12JUN2015:16:35:00.000 
6549 45 15APR2015:16:10:00.000 
6549 45 18APR2015:13:15:00.000 
6549 45 18APR2015:13:18:00.000 
6549 12 22MAY2015:14:15:00.000 
6549 12 22MAY2015:14:20:00.000 
;
run;
data want;
 set have;
 by id1 id2 notsorted;
 if first.id2 or dif(time) gt 10*60 then group+1;
run;

[Vendy]

Thank you very much. It works. Unfortunately it is not possible to mark two answers as 'correct'. Your answer also is correct.

[rgettys]

That is great code. Thanks

[rgettys]

Using Ksharp's method,  how could I reset values each time id1 is changed?

[Ksharp]

Add one more line code.

data want;
 set have;
 by id1 id2 notsorted;
if first.id1 then ........................
 if first.id2 or dif(time) gt 10*60 then group+1;
run;

[Vendy]

Hi,

thank you very much for your answers. It looks very good. Currently I am out of office but tomorrow I will try it and let you know.