topic Re: How do I combine several time intervals into one? in SAS Programming

How do I combine several time intervals into one?

terjeph — Tue, 02 May 2023 23:32:31 GMT

I want to combine two or more time periods into one, contingent upon a) same ID and b) same value on another variable (hours).

Data have:

ID Start End Hours

1 20230101 20230108 8

1 20230109 20230131 8

1 20230201 20230228 4

2 20230101 20230108 8

2 20230110 20230131 8

2 20230201 20230228 4

Data want:

ID Start End Hours

1 20230101 20230131 8

1 20230201 20230228 4

2 20230101 20230108 8

2 20230110 20230131 8

2 20230201 20230228 4

Re: How do I combine several time intervals into one?

SASKiwi — Wed, 03 May 2023 02:03:42 GMT

Try this:

data Have;
input @1 ID $2.  @4 Start yymmdd8.   @14 End yymmdd8. @23 Hours 4.;
format start end yymmddn8.;
datalines;
1  20230101  20230108  8
1  20230109  20230131  8
1  20230201  20230228  4
2  20230101  20230108  8
2  20230110  20230131  8
2  20230201  20230228  4
;
run;

proc sql;
  create table Want as
  select  ID
         ,Hours
		 ,min(start) as start format = yymmddn8.
		 ,max(end) as end format = yymmddn8.
  from Have
  group by id, Hours
  order by id, start, end 
  ;
quit;

Note, my result also collapses ID 2 rows with 8 hours.

Re: How do I combine several time intervals into one?

mkeintz — Wed, 03 May 2023 19:12:55 GMT

PROC SUMMARY (alias PROC MEANS) is intended for exactly this type of result:

proc summary data=have noprint nway;
  output out=want (drop=_type_ _freq_)  min(start)=start max(end)=end;
  class id hours;
run;

However, the output data will be sorted by ID/HOURS, not ID/START.

Edited addition: Please note, as per @Tom 's comment, that this program ignores gaps between individual date ranges having matching ID/HOURS. So the resulting min start and max end may encompass numerous widely separated individual date ranges.

Re: How do I combine several time intervals into one?

Tom — Wed, 03 May 2023 05:00:26 GMT

How large of a GAP between START and the END of the previous interval to you want to accept without making a new interval?

If you do not care about the size of the gap you can use the NOTSORTED option of the BY statement to assign a group id to data that is grouped, but not actually sorted.

data have;
  input ID (Start End) (:yymmdd.) Hours;
  format start end yymmdd10.;
cards;
1  20230101 20230108 8
1  20230109 20230131 8
1  20230201 20230228 4
2  20230101 20230108 8
2  20230110 20230131 8
2  20230201 20230228 4
;

data grouped;
  set have;
  by id hours notsorted;
  if first.id then group=0;
  group+first.hours;
run;

proc summary data=grouped;
  by id group hours;
  output out=want(drop=_type_) min(start)= max(end)= ;
run;

Results

Obs    ID    group    Hours    _FREQ_         Start           End

 1      1      1        8         2      2023-01-01    2023-01-31
 2      1      2        4         1      2023-02-01    2023-02-28
 3      2      1        8         2      2023-01-01    2023-01-31
 4      2      2        4         1      2023-02-01    2023-02-28

Re: How do I combine several time intervals into one?

terjeph — Wed, 03 May 2023 18:06:34 GMT

Thanks! Worked perfectly.

Re: How do I combine several time intervals into one?

terjeph — Tue, 09 May 2023 17:31:53 GMT

I actually do care about gaps between the dates so record 4 and 5 should not collaps. Any suggestions?

Re: How do I combine several time intervals into one?

terjeph — Wed, 10 May 2023 03:20:59 GMT

This was an important comment. In fact I'm interested in situastions were there are gaps between the periods, like in row 4 and 5. How is this implemented in the code you suggested?