Grouping contemporary groups

[ChrisCarroll]

Hi, 

I am trying to make a new variable grouping contemporary groups. I would like to group contemporary groups where the count of the group (count variable) is less than 5 observations only.

As you can see the startdate for each group is the earliest observation according to the start_lactation variable and the enddate for each group is the last observation according to the start_lactation variable. 

I would only like to add contemporary groups together if the number of days between startdate and enddate of the contemporary group is less than 150 days after the new contemporary group has been added. Once a new contemporary group is added the startdate or enddate of that group would change and become the value according to the start_lactation date of the new contemporary group added. To emphasise, if a contemporary group would be added to another group and the number of days between the startdate and enddate is greater than 150 days then it should not be added.

 Contemporary groups of lower than 5 observaions can join any size group once they fit the conditions mentioned above.

In the case of the data below I would like observations 1-7 in the one contemporary group, with the startdate of this group the same (05/02/2011) and the new enddate as 20/04/2011.

Observation 8 and 9 should not be added to any other contemporary group,as if it was added to group 1 the startdate would be 05/02/2011 and the enddate would be 14/10/2011 which is more than 150 days and if it were added to contemporary group 6 then the startdate would be 08/10/2011 and the enddate would be 15/03/12 which is more than 150 days.

Nothing should happen to contemporary group 6 as the count here is more than 5.

I am quite a novice when it come to SAS so an explanation would be brilliant. Any help is greatly appreciated. I am using SAS9.4. Thanks in advance

data WORK.TWOHERDS1;
infile datalines dsd truncover;
input TECHID:13. START_LACTATION:DDMMYY10. contemporarygroup:32. startdate:DDMMYY. enddate:DDMMYY. count:32.;
format TECHID 13. START_LACTATION DDMMYY10. startdate DDMMYY. enddate DDMMYY.;
label TECHID="TECHID";
datalines;
669222149 05/02/2011 1 05/02/11 10/02/11 4
669222145 05/02/2011 1 05/02/11 10/02/11 4
669222150 08/02/2011 1 05/02/11 10/02/11 4
669823432 10/02/2011 1 05/02/11 10/02/11 4
671847527 21/02/2011 2 21/02/11 21/02/11 1
669819685 17/03/2011 3 17/03/11 17/03/11 1
669819684 20/04/2011 4 20/04/11 20/04/11 1
669222144 08/10/2011 5 08/10/11 14/10/11 2
676229644 14/10/2011 5 08/10/11 14/10/11 2
748982347 25/01/2012 6 25/01/12 15/03/12 49
748982346 25/01/2012 6 25/01/12 15/03/12 49
744293316 26/01/2012 6 25/01/12 15/03/12 49
746731229 26/01/2012 6 25/01/12 15/03/12 49
748465662 27/01/2012 6 25/01/12 15/03/12 49
744813559 28/01/2012 6 25/01/12 15/03/12 49
;;;;

[Kurt_Bremser]

You contradict yourself. You start with

where the count of the group (count variable) is less than 5 observations only.

but later state

In the case of the data below I would like observations 1-7 in the one contemporary group

[ChrisCarroll]

Apologies, once the edit has been carried out, there can then be more than 5 in the one contemporary group.

[Kurt_Bremser]

Try this:

data WORK.TWOHERDS1;
input TECHID:$13. START_LACTATION:DDMMYY10. contemporarygroup:32. startdate:DDMMYY. enddate:DDMMYY. count:32.;
format START_LACTATION DDMMYY10. startdate DDMMYY. enddate DDMMYY.;
label TECHID="TECHID";
datalines;
669222149 05/02/2011 1 05/02/11 10/02/11 4
669222145 05/02/2011 1 05/02/11 10/02/11 4
669222150 08/02/2011 1 05/02/11 10/02/11 4
669823432 10/02/2011 1 05/02/11 10/02/11 4
671847527 21/02/2011 2 21/02/11 21/02/11 1
669819685 17/03/2011 3 17/03/11 17/03/11 1
669819684 20/04/2011 4 20/04/11 20/04/11 1
669222144 08/10/2011 5 08/10/11 14/10/11 2
676229644 14/10/2011 5 08/10/11 14/10/11 2
748982347 25/01/2012 6 25/01/12 15/03/12 49
748982346 25/01/2012 6 25/01/12 15/03/12 49
744293316 26/01/2012 6 25/01/12 15/03/12 49
746731229 26/01/2012 6 25/01/12 15/03/12 49
748465662 27/01/2012 6 25/01/12 15/03/12 49
744813559 28/01/2012 6 25/01/12 15/03/12 49
;;;;

data want;
set WORK.TWOHERDS1;
retain newgroup start_group;
if _n_ = 1
then do;
  newgroup = 1;
  start_group = startdate;
end;
if enddate - start_group > 150
then do;
  newgroup + 1;
  start_group = startdate;
end;
drop start_group;
run;

[ballardw]

Define what a "contemporary group" is.

Examples are not sufficient without an actual rule. Consider this example of mapping X to Y:

x     y
1     3
2     3
3     5
4     4
5     4
6     3

without a rule (or rules) you do not have enough information to map 7, 8, or 35. So providing an example based on "row" does not provide a general rule that can be programmed.