DATA Step, Macro, Functions and more

divide rows into groups

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

divide rows into groups

Hi,

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

I have data:

ID1ID2TIME
123412

22MAY2015:16:10:00.000 

12341222MAY2015:16:15:00.000 
12341212JUN2015:6:35:00.000  
12341212JUN2015:16:35:00.000 
654945

15APR2015:16:10:00.000 

65494518APR2015:13:15:00.000 
65494518APR2015:13:18:00.000 
65491222MAY2015:14:15:00.000 
65491222MAY2015: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:

ID1ID2TIMEGROUP
123412

22MAY2015:16:10:00.000 

1
12341222MAY2015:16:15:00.000  1
12341212JUN2015:6:35:00.000   2
12341212JUN2015:16:35:00.000  3
654945

15APR2015:16:10:00.000 

4
65494518APR2015:13:15:00.000  5
65494518APR2015:13:18:00.000  5
65491222MAY2015:14:15:00.000  6
65491222MAY2015:14:20:00.000  6

Thank you.


Accepted Solutions
Solution
‎07-07-2015 09:12 AM
Valued Guide
Posts: 858

Re: divide rows into groups

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=_Smiley Happy;

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;

View solution in original post


All Replies
Super User
Posts: 5,257

Re: divide rows into groups

Job for the data step, not SQL.

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

Data never sleeps
Occasional Contributor
Posts: 13

Re: divide rows into groups

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.

Solution
‎07-07-2015 09:12 AM
Valued Guide
Posts: 858

Re: divide rows into groups

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=_Smiley Happy;

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;

Occasional Contributor
Posts: 13

Re: divide rows into groups

Thank you very much. It works.

Super User
Posts: 9,682

Re: divide rows into groups

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;
Occasional Contributor
Posts: 13

Re: divide rows into groups

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

Occasional Contributor
Posts: 7

Re: divide rows into groups

That is great code. Thanks
Occasional Contributor
Posts: 7

Re: divide rows into groups

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

Super User
Posts: 9,682

Re: divide rows into groups

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;
Occasional Contributor
Posts: 13

Re: divide rows into groups

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 369 views
  • 6 likes
  • 5 in conversation