topic Re: Calculating calving intervals with dates in columns in SAS Programming

Calculating calving intervals with dates in columns

magician — Fri, 15 Sep 2017 11:04:37 GMT

I have the following data:

DATA A;
INPUT cow $ calvingdate :ddmmyy10.;
DATALINES;
1 15/08/2008
4 02/08/2008
4 13/10/2009
4 13/10/2009
4 21/06/2011
4 17/09/2012
14 30/12/2009
14 16/03/2012
18 01/02/2009
18 19/01/2011
27 18/10/2008
34 02/07/2009
34 29/12/2010
34 31/07/2012
37 11/09/2008
37 29/07/2009
37 24/06/2011
39 10/01/2008
39 02/02/2009
39 10/02/2011
40 11/01/2008
41 15/10/2008
41 21/06/2011
44 04/03/2008
44 22/11/2010
44 27/10/2012
45 02/04/2008
45 30/07/2009
47 29/10/2008
49 30/03/2008
49 03/01/2011
51 17/04/2008
53 11/03/2008
53 11/11/2009
53 10/12/2010
53 26/10/2012
54 07/07/2008
54 03/03/2010
54 06/02/2011
54 17/12/2012
54 26/01/2014
54 26/01/2014
55 06/05/2008
55 15/09/2009
55 01/08/2011
55 01/08/2011
56 27/12/2008
56 12/08/2010
56 03/10/2011
56 08/09/2012
56 26/10/2013
58 15/10/2008
59 29/10/2008
59 16/09/2009
61 23/10/2008
61 10/08/2010
62 06/08/2008
62 13/07/2009
64 16/10/2008
64 07/08/2010
64 16/09/2011
64 21/10/2012
66 09/04/2009
66 30/07/2010
66 31/07/2012
66 06/01/2014
66 05/10/2015
68 15/09/2008
68 21/06/2011
68 06/01/2014
69 10/09/2008
69 28/07/2012
69 25/09/2013
69 01/09/2014
76 25/08/2009
76 31/07/2011
76 14/03/2013
77 25/05/2009
77 17/02/2011
79 11/07/2009
79 13/08/2010
79 02/03/2012
79 20/10/2013
80 30/07/2009
81 04/07/2009
82 20/06/2009
82 09/02/2011
82 25/10/2012
83 07/11/2009
83 13/12/2010
83 07/12/2011
83 07/12/2011
86 31/08/2009
86 31/12/2010
86 10/02/2013
87 09/06/2009
87 24/02/2012
90 22/07/2009
90 29/10/2011
90 16/02/2013
90 24/03/2014
91 15/11/2009
91 01/09/2011
92 18/07/2010
92 16/07/2011
92 12/03/2013
92 09/10/2014
92 19/11/2016
93 27/08/2010
93 05/10/2011
93 22/12/2012
93 07/10/2014
95 15/01/2012
95 15/01/2012
95 29/09/2014
95 16/01/2016
98 08/03/2011
98 20/03/2012
98 14/08/2013
98 15/11/2014
98 17/11/2015
98 18/11/2016
99 30/08/2010
99 14/02/2012
99 11/08/2013
102 25/07/2010
102 25/09/2011
102 04/03/2013
102 22/01/2014
102 25/07/2015
104 03/08/2010
104 13/07/2011
104 27/09/2012
104 13/02/2014
104 29/01/2015
104 14/11/2016
105 19/08/2010
105 14/07/2011
105 09/10/2012
105 04/09/2013
106 21/11/2010
106 06/12/2011
106 06/12/2011
106 03/08/2013
106 20/08/2014
107 07/08/2010
107 11/10/2011
107 06/12/2012
107 20/02/2015
108 23/08/2010
108 29/07/2011
108 29/07/2011
108 08/09/2013
108 29/08/2014
108 25/08/2015
111 16/07/2011
111 29/12/2012
112 04/10/2010
112 14/09/2011
112 10/08/2012
112 10/08/2012
112 27/02/2014
112 27/02/2014
112 16/03/2016
113 07/03/2011
113 31/07/2012
113 29/08/2013
113 29/08/2013
113 29/10/2014
113 03/03/2016
114 09/03/2011
114 25/02/2012
114 31/10/2013
114 06/04/2015
115 08/07/2011
115 08/07/2011
116 13/07/2011
118 01/08/2011
119 19/10/2011
119 19/10/2013
120 13/07/2011
120 17/10/2012
120 24/09/2013
120 24/09/2013
120 13/10/2014
120 25/08/2015
120 06/01/2017
123 27/07/2011
123 23/03/2014
126 14/07/2011
126 17/12/2012
126 01/02/2014
126 26/09/2015
126 04/01/2017
129 02/11/2011
129 05/09/2013
129 03/08/2015
129 03/08/2015
130 07/11/2011
130 12/02/2013
130 03/02/2014
131 19/01/2012
131 26/12/2013
132 19/07/2012
133 30/11/2011
133 08/12/2012
133 08/12/2012
133 23/12/2013
133 18/08/2015
133 28/10/2016
133 28/10/2016
134 23/09/2012
134 16/12/2013
134 17/02/2016
134 11/03/2017
137 24/09/2012
137 04/09/2013
137 11/10/2014
137 02/10/2015
137 30/01/2017
138 22/12/2012
138 05/03/2014
138 31/08/2015
138 14/01/2017
139 23/09/2012
139 05/09/2013
140 07/09/2012
140 28/08/2014
140 08/09/2015
140 22/12/2016
142 24/09/2012
142 26/12/2013
142 26/10/2015
142 14/11/2016
143 26/09/2012
143 21/09/2013
144 01/10/2012
144 12/09/2013
144 06/03/2015
145 10/02/2013
145 01/01/2015
145 14/11/2016
146 15/10/2012
146 19/11/2014
146 11/11/2015
146 21/12/2016
147 23/09/2012
147 25/08/2014
147 20/08/2015
147 30/10/2016
148 09/08/2013
148 04/02/2015
149 14/03/2013
149 12/12/2014
149 11/09/2016
150 31/03/2013
150 18/03/2014
150 28/01/2016
150 07/01/2017
151 30/04/2013
151 05/10/2014
151 27/01/2016
152 06/09/2013
153 01/04/2013
153 18/03/2014
153 06/10/2015
153 12/10/2016
154 14/08/2013
154 14/10/2015
156 03/04/2013
156 30/07/2014
156 05/10/2015
156 22/09/2016
158 06/09/2013
158 20/07/2015
158 21/09/2016
162 24/09/2013
162 20/11/2014
162 05/10/2015
162 24/10/2016
164 07/12/2013
165 03/02/2014
165 11/03/2015
165 27/08/2016
167 28/07/2014
169 28/03/2014
169 02/10/2015
169 17/10/2016
174 10/09/2014
174 23/02/2017
176 19/11/2014
176 05/01/2017
177 14/09/2014
177 07/09/2015
179 14/09/2014
179 22/08/2015
183 30/11/2014
184 20/01/2015
184 10/03/2016
185 02/03/2015
188 16/02/2015
188 28/10/2016
189 12/08/2015
189 24/01/2017
190 20/03/2015
190 16/11/2016
197 28/09/2015
200 16/12/2016
202 22/09/2015
202 30/10/2016
204 02/10/2015
204 01/11/2016
205 01/01/2016
207 23/03/2016
209 22/03/2016
211 19/01/2017
213 23/01/2017
216 18/10/2016
218 17/08/2016
219 07/01/2017
222 09/01/2017
227 11/12/2016
228 11/10/2016
230 04/02/2017
231 01/02/2017
232 01/10/2016
311 20/10/2015
315 30/09/2015
386 29/01/2009
415 23/09/2015
415 10/01/2017
506 27/09/2015
506 24/12/2016
895 26/10/2008
941 16/08/2008
946 18/01/2008
948 01/02/2009
953 04/01/2008
962 04/02/2008
981 08/01/2008
981 08/03/2009
982 18/03/2009
982 30/03/2011
988 20/01/2008
988 12/09/2009
989 23/11/2008
992 16/10/2008
995 21/03/2009
999 05/02/2009
999 18/12/2010
1386 10/08/2010
1386 27/02/2012
2362 03/12/2008
2362 09/12/2009
2821 07/01/2008
5065 03/10/2015
5065 25/11/2016
5167 14/10/2015
5167 01/11/2016
5253 27/09/2015
5254 28/09/2015
5301 22/09/2015
5332 24/09/2015
5332 25/10/2016
5501 22/03/2017
7029 11/06/2009
7124 31/05/2009
7178 00/00/2009

Has you can see each number on the right represent a single cow, and on the left are the calving dates. There is multiple dates for one single cow and some other individuals only have one date.

I would like to calculate the difference or amount of days between each group of dates of one individual cow who present multiple calving dates. Cows who only present one date are excluded. (Ex. Cow 1, Cow 7029, Cow 7124, Cow 7178)

I really have no idea what should i do. I will take any advice.

Re: Calculating calving intervals with dates in columns

Kurt_Bremser — Fri, 15 Sep 2017 11:50:43 GMT

Sort by cow and calvingdate, and run a data step by cow, using the lag() function and the first. and last. variables:

DATA A;
INPUT cow  calvingdate :ddmmyy10.;
format calvingdate ddmmyy10.;
DATALINES;
1 15/08/2008
4 02/08/2008
4 13/10/2009
4 13/10/2009
4 21/06/2011
4 17/09/2012
14 30/12/2009
14 16/03/2012
18 01/02/2009
18 19/01/2011
27 18/10/2008
34 02/07/2009
34 29/12/2010
34 31/07/2012
37 11/09/2008
37 29/07/2009
37 24/06/2011
39 10/01/2008
39 02/02/2009
39 10/02/2011
40 11/01/2008
41 15/10/2008
41 21/06/2011
44 04/03/2008
44 22/11/2010
44 27/10/2012
;
run;

proc sort data=a;
by cow calvingdate;
run;

data want;
set a;
by cow;
if not (first.cow and last.cow); * this removes cows with only one observation;
diff = calvingdate - lag(calvingdate);
if first.cow then diff = .;
run;

Note that I reduced the example data to a size that is sufficient to illustrate the issue.

Re: Calculating calving intervals with dates in columns

PeterClemmensen — Fri, 15 Sep 2017 11:51:15 GMT

Just to be sure I understand this correctly, For each cow that has multiple calvingdates, you want the difference between the first and the latest calvingdate?

Re: Calculating calving intervals with dates in columns

HB — Fri, 15 Sep 2017 17:11:29 GMT

Just to be sure I understand this correctly, For each cow that has multiple calvingdates, you want the difference between the first and the latest calvingdate?

Good question. Or is it that you want to know that cow 4 went about 14 months between birth 1 and 2 (twins?), about 8 months between 2/3 and 4, and about 15 months between 4 and 5 (which is what Kurt's solution does)?

4 02/08/2008
4 13/10/2009
4 13/10/2009
4 21/06/2011
4 17/09/2012

Re: Calculating calving intervals with dates in columns

mkeintz — Fri, 15 Sep 2017 17:51:25 GMT

If @magician just wants the range between the first and last calf, then the last step in @Kurt_Bremser's program can be converted to the below, which produces one record per cow:

data want;
  set a;
  by cow;
  if first.cow ^= last.cow;
  diff=dif(calvingdate);
  if last.cow;
run;

The "if first.cow ^= last.cow" will allow only the first and last records from only multi-calf cows.
The DIF function [dif(x) = x-lag(x)] will generate the difference in dates between the last calf of one cow and the first calf of the next cow. But the subsequent "if last.cow" filter keeps only cases in which the DIFF value is appropriate.

Re: Calculating calving intervals with dates in columns

magician — Sat, 16 Sep 2017 01:17:56 GMT

Yes exactly! Thank you!

Re: Calculating calving intervals with dates in columns

magician — Sat, 16 Sep 2017 02:55:50 GMT

Thank you!!

You guys are life savers!

Re: Calculating calving intervals with dates in columns

magician — Sat, 16 Sep 2017 09:44:40 GMT

Hello, sorry for bothering you again, i just got one more question:

If i would like to know the interval of days between each different date for cows with multiple dates (More that 2 dates) what can i do?

Ex.
Cow Calving date
4 02/08/2008
4 13/10/2009
4 21/06/2011
4 17/09/2012

What are the interval or range of days between 02/08/2008 and 13/10/2009, and 13/10/2009 to 21/06/2011 and so and so.

What can i do or modify to achieve this?

Thanks in advance btw

Re: Calculating calving intervals with dates in columns

Kurt_Bremser — Sat, 16 Sep 2017 15:52:41 GMT

@magician wrote:
Hello, sorry for bothering you again, i just got one more question:

If i would like to know the interval of days between each different date for cows with multiple dates (More that 2 dates) what can i do?

Ex.
Cow Calving date
4 02/08/2008
4 13/10/2009
4 21/06/2011
4 17/09/2012

What are the interval or range of days between 02/08/2008 and 13/10/2009, and 13/10/2009 to 21/06/2011 and so and so.

What can i do or modify to achieve this?

Thanks in advance btw

Use my code.

Re: Calculating calving intervals with dates in columns

magician — Sun, 17 Sep 2017 09:15:39 GMT

Oh right!

Sorry i discovered some errors in my data that were conflicting, but now everything works!

Thank you!! you guys are great!