calculating calving interval

[chappy]

I have the following data

Year           CowID         CowAge           CalfID                birthdate      

2007          M234                7                   T102                  17MAR2007

2007          R314                2                    T081                  03MAR2007

2007          P118                5                    T011                  01MAR2007

2007          L187                8                     T134                 19MAR2007

2007          K222               9                     T123                  18MAR2007

2007          R115               2                    T101                   17MAR2007

etc for year 2007...

also subseqent years with similar data.

I would like to calculate when the 3rd mature cow (CowAge > 2) has a calf (i.e the birthdate) as the start of the calving season. I would then like to calculate when the rest of the cows have a calf relative this date (when the 3rd mature cow has a calf). This number could be negative or positive (or zero if the cow happens to have a calf on the same day as the 3rd mature cow). I would then like to use these days to calculate the percentage of cows having a calf in the first 21 days, first 42 days, first 63 days and greater than 63 days.

SO far I have managed to do the following:

Sort data by birthdate and year

proc sort data=have;

by birthdate year;

run;

Create a count column for the mature cows for each year

data want;

set data have;

count+1;

by year;

where cowage >2;

if first.year then count=1;

run;

Identify the 3rd Mature cow calving (Start of calving season)

data want2;

set want;

startcalve = birthdate;

where count=3;

by year;

run;

I have tried merging want and want 2 based on calfID but then only the calfID corresponding to the startcalve date has data. Any help on how to further calculate the day of calving based on the startcalve date would be greatly appreciated.

Thanks.

[Reeza · Accepted solution]

Merge in the want2 by year, because the calving date would be the same for the full year wouldn't it? Also, do you want to merge to want or to the original have dataset? Are you interested only in cows over 2?

Then you can subtract the birthdate and the startcalve date and apply a format or if statements to group the dates.  You can add a keep statement to want2 to keep only the year and startcalve date to help keep things clear.

Sort data by birthdate and year

proc sort data=have;

by birthdate year;

run;

Create a count column for the mature cows for each year

data want;

set data have;

by year;

where cowage >2;

count+1;

if first.year then count=1;

run;

Identify the 3rd Mature cow calving (Start of calving season)

data want2;

set want;

by year;

where count=3;

startcalve = birthdate;

keep year startcalve;

run;

Merge it in:

data want3;

merge want want2;

by year;

run;

[chappy]

Thanks! That worked great