topic Re: Grouping observations by days apart in New SAS User

Grouping observations by days apart

ChrisCarroll — Mon, 11 May 2020 17:21:24 GMT

Hi all,

I'm currently attempting to group observations where the days between observations is less than 10. The new column can be called group. I would like the title of the observations in a group to be the earliest date according to start_lactation. In the case of the data below, I would like observations 1-4 with the group column titled as 05/02/2011. Observations 5-7 should be left as blank. Observations 8 and 9 should be titled as 08/10/2011. Observations 10-15 should be titled as 25/01/2012. I need to do this for thousands of observations. I'm using sas 9.4.

Any help is greatly appreciated

data WORK.X;
infile datalines dsd truncover;
input TECHID:13. START_LACTATION:DDMMYY10. Daysapart:32.;
format TECHID 13. START_LACTATION DDMMYY10.;
label TECHID="TECHID";
datalines;
669222149 05/02/2011 .
669222145 05/02/2011 0
669222150 08/02/2011 3
669823432 10/02/2011 2
671847527 21/02/2011 11
669819685 17/03/2011 24
669819684 20/04/2011 34
669222144 08/10/2011 171
676229644 14/10/2011 6
748982347 25/01/2012 103
748982346 25/01/2012 0
744293316 26/01/2012 1
746731229 26/01/2012 0
748465662 27/01/2012 1
744813559 28/01/2012 1

Re: Grouping observations by days apart

Reeza — Mon, 11 May 2020 17:29:16 GMT

data want;
set x;
by techid start_lactation;*may need to presort your data;

retain group group_start_date;
if first.techid then do;
group=1; 
group_start_date = start_lactation;
end;
else if daysApart>10 then do;
  group+1; 
   group_start_date = start_lactation;
end;

run;

@ChrisCarroll wrote:

Hi all,

I'm currently attempting to group observations where the days between observations is less than 10. The new column can be called group. I would like the title of the observations in a group to be the earliest date according to start_lactation. In the case of the data below, I would like observations 1-4 with the group column titled as 05/02/2011. Observations 5-7 should be left as blank. Observations 8 and 9 should be titled as 08/10/2011. Observations 10-15 should be titled as 25/01/2012. I need to do this for thousands of observations. I'm using sas 9.4.

Any help is greatly appreciated

data WORK.X;
infile datalines dsd truncover;
input TECHID:13. START_LACTATION:DDMMYY10. Daysapart:32.;
format TECHID 13. START_LACTATION DDMMYY10.;
label TECHID="TECHID";
datalines;
669222149 05/02/2011 .
669222145 05/02/2011 0
669222150 08/02/2011 3
669823432 10/02/2011 2
671847527 21/02/2011 11
669819685 17/03/2011 24
669819684 20/04/2011 34
669222144 08/10/2011 171
676229644 14/10/2011 6
748982347 25/01/2012 103
748982346 25/01/2012 0
744293316 26/01/2012 1
746731229 26/01/2012 0
748465662 27/01/2012 1
744813559 28/01/2012 1

Re: Grouping observations by days apart

ChrisCarroll — Mon, 11 May 2020 18:54:16 GMT

Thanks that worked well. Is there a way of grouping the "group" column if there are less than 5 observations in any particular group and only if the group_start_date between the consecutive groups is less than 100 days. Thanks.

Re: Grouping observations by days apart

Reeza — Mon, 11 May 2020 20:59:18 GMT

@ChrisCarroll wrote:

Thanks that worked well. Is there a way of grouping the "group" column if there are less than 5 observations in any particular group and only if the group_start_date between the consecutive groups is less than 100 days. Thanks.

You could add a counter to track the number of observations per group? And then add another set of logic to analyze the data by groups to see if the 100 days between group starts.