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.
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;
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;
Thanks! That worked great
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.