BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Vendy
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Steelers_In_DC
Barite | Level 11

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;

View solution in original post

10 REPLIES 10
LinusH
Tourmaline | Level 20

Job for the data step, not SQL.

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

Data never sleeps
Vendy
Obsidian | Level 7

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
Barite | Level 11

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
Obsidian | Level 7

Thank you very much. It works.

Ksharp
Super User

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
Obsidian | Level 7

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

rgettys
Fluorite | Level 6
That is great code. Thanks
rgettys
Fluorite | Level 6

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

Ksharp
Super User

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
Obsidian | Level 7

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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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