topic Re: divide rows into groups in SAS Programming

divide rows into groups

Vendy — Tue, 07 Jul 2015 11:37:40 GMT

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.

Re: divide rows into groups

LinusH — Tue, 07 Jul 2015 12:14:13 GMT

Job for the data step, not SQL.

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

Re: divide rows into groups

Vendy — Tue, 07 Jul 2015 12:18:19 GMT

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.

Re: divide rows into groups

Steelers_In_DC — Tue, 07 Jul 2015 13:12:55 GMT

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;

Re: divide rows into groups

Ksharp — Tue, 07 Jul 2015 13:28:25 GMT

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;

Re: divide rows into groups

Vendy — Tue, 07 Jul 2015 14:02:34 GMT

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.

Re: divide rows into groups

Vendy — Wed, 08 Jul 2015 06:27:17 GMT

Thank you very much. It works.

Re: divide rows into groups

Vendy — Wed, 08 Jul 2015 06:31:52 GMT

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

Re: divide rows into groups

rgettys — Wed, 05 Apr 2017 22:46:40 GMT

That is great code. Thanks

Re: divide rows into groups

rgettys — Wed, 05 Apr 2017 22:54:11 GMT

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

Re: divide rows into groups

Ksharp — Thu, 06 Apr 2017 12:41:17 GMT

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;