04-01-2016 03:38 PM - edited 04-01-2016 03:39 PM
Good evening SAS users:
I'm glad, always, to be helped here by your knowledge, i know that sometimes those questions are more linked to SQL programming, for that reason i come here to ask for your help. As many users here know, i work in quantitative genetics, and almost finishing to build an information bank in beef cattle, i have an interesting example to be solved, expecting for your help, well let's begin:
As You know, reproductive efficiency is important to be tested in genetic breeding programs, this in fact is 50% to count at the time to take decisions, in the practice of farming every day. So here i have a very nice sample about what we (me and my colleagues) are studying into sheep beef cattle.
Here is the Table made to be evaluated:
Explaining the table above, the first column shows the reproductive season (REPRODUCTIVE_SEASON), each year the far has 2 parts into the reproductive season the BREEDING and PARTURITION, the example i show here show the reproductive season on 1999, and reproductive season on 2000, this due some EWES (DAMs) are having newborns each reproductive season, so each year (indeed). the next column shows the date where the DAM (EWE) was born, this will be used to estimate the age (in days) at any of the life/biological events (occurrences) associated with the reproductive efficiency for this little example. Repeating the info here, this example is a sample of an full pedigree, accomplishing more or less than 10 thousand of observations.
The next 2 columns show information of the date when SIRE (RAM) was born too, this will help us to estimate the genetic male efficiency at reproduction, this fact is important, due the fertility must be corrected by the SIRE efficiency, in this farm all SIRES are randomly distributed each REPRODUCTIVE_SEASON, for that reason this research cant pass bye any bias offered by SIRES.
The next column contains information of DAM_WEIGHT in general terms, looking as a crossed table it indicates the WEIGHT of the DAM at BREEDING event, and PARTURITION event, this is important to estimate the pregnancy effects above the weight of theDAM at the beginning (BREEDING) at the end (PARTURITION) of the REPRODUCTIVE_SEASON, this correlated to the NEW BORN WEIGHT.
The column called DATE, simply contains information of the dates related when the BREEDING (insemination) was done and the PARTURITION event happened, i put this information arrangement together cause they are linked to the same DAM and for the purpose of this analysis that will help to be considered in the results.
The NEW_BORN_SEX, NEW_BORN_ID and NEW_BORN_LIVE_DEATH, are showing information related to the sex of the LAMB, the number identifying the same LAMB and the live status at the parturition time, varying from DEATH "0" or LIVE "1", both options could happen in the same parturition, the problems related to happen a lamb to death at time of parturition are under research, for tat reason we need to use this.
Using all the information recorded in the table above, we need to estimate the next Traits:
REPRO_ORDER: Is the ORDER in which all DAMs begin and continue the reproductive functions into the farm, in our example year 1999 is the fist reproduction labor of those DAMs.
EFFECTIVE_SIRE: The effective SIRE, is the last SIRE used to inseminate the DAM to produce progeny in this breeding season, in this example could be observed that any of those DAMS used 3 inseminations (repetition), then the last insemination is taken as effective insemination. Some times the last insemination is using the genetic material of the same MALE or another one, it depended of the availability of genetic material or due the owner's farm decision.
PARTURITION_DATE: This is the same date of the parturition date in the first TABLE. related to each DAM.
PREGNANCY_LENGTH: Is the duration in days beginning from the effective insemination until the parturition day, this varies individually in each DAM.
WEIGHT_GAIN_BREED_PART_DAYS: This helps us to estimate the weight gain from the INSEMINATION until the PARTURITION, measures obtained at the insemination day (the effective insemination) and at the parturition, as initial weight and final weight, divided by PREGNANCY_LENGTH in days, we will obtain daily weight gained in kilograms, very important information to study the metabolically development at pregnancy of the dam.
AGE_AT_LAMBING_DAYS: Is the difference in days from the day when the DAM was born and the day when she gave newborns (concluding this concepts i'll show a table indicating this)
BREED_REPET: How many times the DAM was inseminated to accomplish pregnancy.
BREEDING_EFFICIENCY: The Efficiency in percentage of the BREED_REPET, example: if the DAM was inseminated 2 times, the efficiency is 50%, 3 times is 33.3% and so on.
FERTILITY: Is measured at parturition time, if the female was inseminated and produced newborns, her fertility will be "1", if not will be "0".
BIRTH_TYPE: Is the result in the parturition, how many newborns the DAM produced this REPRODUCTIVE_SEASON, varying from 1 until 5 (this event is rare but it happens), genetically not good but it is not discussion of this example, we just want to describe the situations here.
LIVE_NEW_BORN: Reflect how many new_borns were alive at parturition time, this information is extracted from the column NEW_BORN_LIVE_DEATH. THE VALUE FOR BORN IS 1
PERCENT_LIVE_NEW_BORN: Is the percentage of newborns alive at parturition, example: if there are 2 newborns and both are alive, then there are 100% of surviving newborns, if there are 3 newborns and 2 of them are death, then there are 33% of surviving new borns in this litter.
LIVE_NEW_BORN: Reflect how many new_borns were dead at parturition time, this information is extracted from the column NEW_BORN_LIVE_DEATH. THE VALUE FOR BORN IS 0
PERCENT_DEATH_NEW_BORN: Is the opposite of PERCENT_LIVE_NEW_BORN, it's just 100-(PERCENT_LIVE_NEW_BORN)
MALE: It indicates how many male lamb(s) are into this litter produced
PERCENT_MALE: Is the Percentage of males in this litter
FEMALE: It indicates how many female lamb(s) are into this litter produced
PERCENT_FEMALE: Is the Percentage of males in this litter
LITTER_WEIGHT: Weight of the litter (in Kilograms) this is obtained from the column NEW_BORN_WEIGHT, resulting of the summation of weight of each individual newborn, if there is an unique newborn, then the litter size will the this weight though.
MEAN_WEIGHT_NEW_BORN: Results of the division of the total weight (in kilograms) of the litter by the number of individuals born in this female in this parturition, example: the Total weight (in Kilograms) of the litter is 5.6 Kg. there are 2 individuals produced in this litter, so the MEAN_WEIGHT_NEW_BORN will be " 5.6 / 2 = 2.8 "
As result of the information above: table and traits obtained based in the table; the table to be produced is:
Evaluating this data set, i tried to explain the realistic facts are observed in the farm. i would like to point specifically in these 2 special situations here, the DAM 3264 was inseminated 2 times, but she did not gave newborns indicating null fertility "0" and the other parameters are in blank. this will offer very important situation to know and study the real factors involved causing null fertility.
And the DAMs 3795 and 4662, i put them in 2 REPRODUCTIVE_SEASONs, the first one in 1999 and the second one in 2000, indicated in the first column of the resulting table REPRO_ORDER, indicating that in the year 1999 was the first recording of reproduction activity of this female, and year 200 was the second appearance to be mated and produce progeny.
At this time, i hope you can help me to program this SAS statements, i always appreciate your help, it will be almost 1 year and the half sharing with you my examples and always will appreciate your help.
I'll attach one EXCEL file where in the first window is located the fisrt table, and the second window the result expected, the other file is an CSV file where is located the full code of SAS.
The Full code to generate the fistr table in SAS is:
input REPRODUCTIVE_SEASON$1-32 DAM_BORN_DATE:mmddyy10. DAM SIRE_BORN_DATE:mmddyy10. SIRE DAM_WEIGHT DATE:mmddyy10. NEW_BORN_WEIGHT NEW_BORN_SEX$ NEW_BORN_ID LIVE_DEATH ;
format DAM_BORN_DATE mmddyy10.;
format SIRE_BORN_DATE mmddyy10.;
format DATE mmddyy10.;
BREEDING_1999 11/22/1995 4578 07/22/1993 2565 35 01/01/1999 . . . .
PARTURITION_1999 11/22/1995 4578 07/22/1993 2565 40 06/05/1999 4.0 M 5223 1
BREEDING_1999 11/20/1995 4665 06/11/1995 739 38 01/01/1999 . . . .
BREEDING_1999 11/20/1995 4665 08/07/1995 5794 36 01/01/1999 . . . .
PARTURITION_1999 11/20/1995 4665 08/07/1995 5794 42 06/03/1999 3.7 F 5224 1
BREEDING_1999 06/15/1996 3264 10/10/1995 555 42 01/01/1990 . . . .
BREEDING_1999 06/15/1996 3264 10/10/1995 555 42 01/25/1999 . . . .
BREEDING_1999 06/23/1996 4662 11/11/1995 2794 40 01/02/1999 . . . .
PARTURITION_1999 06/23/1996 4662 11/11/1995 2794 45 06/02/1999 3.0 M 5225 1
PARTURITION_1999 06/23/1996 4662 11/11/1995 2794 45 06/02/1999 2.7 F 5226 0
BREEDING_1999 09/15/1994 7411 05/13/1994 142 41 06/14/1999 . . . .
BREEDING_1999 09/15/1994 7411 04/04/1994 258 41 06/30/1999 . . . .
PARTURITION_1999 09/15/1994 7411 04/04/1994 258 50 11/18/1999 1.5 F 5300 1
PARTURITION_1999 09/15/1994 7411 04/04/1994 258 50 11/18/1999 2.0 M 5301 0
PARTURITION_1999 09/15/1994 7411 04/04/1994 258 50 11/18/1999 1.5 F 5302 0
BREEDING_1999 03/19/1993 3795 03/30/1995 3794 40 01/01/1999 . . . .
BREEDING_1999 03/19/1993 3795 03/30/1995 3794 39 01/22/1999 . . . .
PARTURITION_1999 03/19/1993 3795 03/30/1995 3794 45 06/02/1999 2.9 M 5330 1
PARTURITION_1999 03/19/1993 3795 03/30/1995 3794 45 06/02/1999 2.7 M 5331 1
BREEDING_1999 05/13/1992 2235 07/25/1995 5789 39 01/13/1999 . . . .
BREEDING_1999 05/13/1992 2235 10/10/1995 555 39 01/30/1999 . . . .
PARTURITION_1999 05/13/1992 2235 10/10/1995 555 44 06/30/1999 2.2 M 5694 1
PARTURITION_1999 05/13/1992 2235 10/10/1995 555 44 06/30/1999 2.3 F 5695 1
PARTURITION_1999 05/13/1992 2235 10/10/1995 555 44 06/30/1999 2.6 F 5696 1
BREEDING_2000 06/23/1996 4662 04/04/1994 258 42 03/20/2000 . . . .
PARTURITION_2000 06/23/1996 4662 04/04/1994 258 45 09/11/2000 3.0 M 6000 .
BREEDING_2000 03/19/1993 3795 12/25/1995 7846 38 02/11/2000 . . . .
BREEDING_2000 03/19/1993 3795 12/25/1995 7846 38 02/28/2000 . . . .
BREEDING_2000 03/19/1993 3795 03/30/1995 3794 38 03/15/2000 . . . .
PARTURITION_2000 03/19/1993 3795 03/30/1995 3794 38 09/26/2000 2.8 F 6015 1
King Regards for you SAS users
Thank you very much