topic Re: how do i collapse data periods given more conditions in SAS Programming

how do i collapse data periods given more conditions

user40 — Thu, 11 May 2023 10:29:54 GMT

I want to combined two or more time periods into one, contigent upon a) same ID , b) same values of (hours) or other variables indicator if they have. If there is a gap between two periods of more than two days then this should still be two periods. Ses the second row for ID 2 below.

Data have:

ID Startdate Enddate Hours

1 20230101 20230108 8

1 20230109 20230131 8

1 20230201 20230228 4

2 20230101 20230108 8

2 20230112 20230131 8

2 20230201 20230228 4

Data want:

ID Startdate Enddate Hours

1 20230101 20230131 8

1 20230201 20230228 4

2 20230101 20230108 8

2 20230112 20230131 8

2 20230201 20230228 4

Re: how do i collapse data periods given more conditions

yabwon — Thu, 11 May 2023 11:16:50 GMT

You could try like this:

data have;
input ID (Startdate Enddate) (:yymmdd10.) Hours;
format Startdate Enddate yymmdd10.;
cards;
1 20230101 20230108 8
1 20230109 20230131 8
1 20230201 20230228 4
2 20230101 20230108 8
2 20230112 20230131 8
2 20230201 20230228 4
;
run;

data interim;
  set have;
  g+1;
  do d=Startdate,Enddate;
    format d yymmdd10.;
    output;
  end;
  drop Startdate Enddate;
run;
proc sort data=interim nodupkey;
  by ID d Hours;
run;

data interim2;
set interim;
by ID d Hours;

grp + ifn(dif(d)>2 and dif(g),1,0);

run;



data want;
  retain ID Startdate d Hours;
  set interim2;
  by ID Hours grp NOTSORTED;

  if first.grp then Startdate=d;

  if last.grp then output;

  
  format Startdate yymmdd10.;
  rename d=Enddate; drop grp g;
run;

[EDIT:] There is quiet assumption, that start-end periods do not overlap.

Bart

Re: how do i collapse data periods given more conditions

user40 — Thu, 11 May 2023 12:12:49 GMT

It did not work for me

Re: how do i collapse data periods given more conditions

user40 — Thu, 11 May 2023 12:22:23 GMT

I cannot see that it would work for me. Have you another solution that are more faster?

Re: how do i collapse data periods given more conditions

yabwon — Thu, 11 May 2023 13:55:00 GMT

You wrote "It did not work for me" but did not wrote anything more. Quite hard to start witch such answer.

What didn't work?

Do you have a log?

Re: how do i collapse data periods given more conditions

user40 — Mon, 15 May 2023 07:10:52 GMT

No, I don't know.