BookmarkSubscribeRSS Feed
user40
Calcite | Level 5

 

 

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

5 REPLIES 5
yabwon
Amethyst | Level 16

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

 

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



user40
Calcite | Level 5
It did not work for me
yabwon
Amethyst | Level 16

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? 

 

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



user40
Calcite | Level 5

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

user40
Calcite | Level 5

No, I don't know. 

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 5 replies
  • 1449 views
  • 0 likes
  • 2 in conversation