## Ordering Data from a pedigree - Females and birth order

Solved
Frequent Contributor
Posts: 115

# Ordering Data from a pedigree - Females and birth order

good day SAS friends:

I will begin with another topic into research:

Well, as you know in genetics we have an important subject called "PEDIGREE", it help us to know better about the population genetics. Into this i need to make an arrange like this: my initial table goes like this:

data have;

input ANIMAL    DAM    SIRE    DAM_BORN_DATE\$    ANIMAL_BIRTH_DATE\$    ANIMAL_BIRTH_TYPE    ANIMAL_BIRTH_WEIGHT;

cards;
6561    4597    5982    06/13/2002    08/05/2008    1    4.8
6870    6561    5878    08/05/2008    10/13/2009    1    3.9
6971    6561    6626    08/05/2008    10/27/2010    3    2.3
6972    6561    6626    08/05/2008    10/27/2010    3    1.9
6973    6561    6626    08/05/2008    10/27/2010    3    2.2
7284    6561    6467    08/05/2008    12/21/2011    2    3.6
7285    6561    6467    08/05/2008    12/21/2011    2    3.3
7603    6561    7069    08/05/2008    05/07/2014    2    4
7603.1    6561    7069    08/05/2008    05/07/2014    2    3.2
;

Well, ill begin, this example is really interesting to make an complete comand line, because it will help to analize maternal traits in mammals.

As you can see the animal 6561 is our study case (example), her (because is a female) was born at 08/05/2008 (MM/DD/YYYY), and in the farm she (6561) was kept to become mother (DAM), later she mate and produce newborns as you can see the year 2009, 2010, 2011 and 2014; one, three ,two ,two and one newborns each year respectively.

My new table (outpu that i am looking for may be like this)

 Parturition_Order Newborn DAM SIRE DAM_BORN_DATE DAM_BIRTH_TYPE DAM_BIRTH_WEIGHT ANIMAL_BIRTH_DATE ANIMAL_BIRTH_TYPE ANIMAL_BIRTH_WEIGHT 1 6870 6561 5878 08/05/2008 1 4.8 10/13/2009 1 3.9 2 6971 6561 6626 08/05/2008 1 4.8 10/27/2010 3 2.3 2 6972 6561 6626 08/05/2008 1 4.8 10/27/2010 3 1.9 2 6973 6561 6626 08/05/2008 1 4.8 10/27/2010 3 2.2 3 7284 6561 6467 08/05/2008 1 4.8 12/21/2011 2 3.6 3 7285 6561 6467 08/05/2008 1 4.8 12/21/2011 2 3.3 4 7603 6561 7069 08/05/2008 1 4.8 05/07/2014 2 4 4 7603.1 6561 7069 08/05/2008 1 4.8 05/07/2014 2 3.2

This output refers as how this female is related to its own birth, after this i could know that the year 2009 ocurred her first lambing, 2010 the second lambing, 2011 the third and the last lambing at year 2014.

in the input, we can observe that the input trait ANIMAL_BIRTH_TYPE  of this female is one ("1"), so in the output it becomes DAM_BIRTH_TYPE, and the Animal_birth_day was 08/05/2008, becomes into DAM_BORN_DATE, following the same contitution, is the ANIMAL_BIRTH_WEIGHT, she was born with 4.8 kilograms, in the output it turn to DAM_BIRTH_WEIGHT.

I hope to make an good description in the example, because this is important to find this information in a data set more that 1000 records.

I'll be waiting for your help, i really need it.

Thak you very much

Accepted Solutions
Solution
‎12-23-2015 01:24 PM
Posts: 5,543

## Re: Ordering Data from a pedigree - Females and birth order

A classic case of self-join:

``````
data haveParturition;
set have;
by dam sire animal_birth_date notsorted;
if first.dam then Parturition_Order = 0;
if first.animal_birth_date then Parturition_Order + 1;
run;

proc sql;
create table want as
select
a.Parturition_Order,
a.animal as newborn,
a.dam,
a.sire,
a.dam_born_date format=mmddyy10.,
b.ANIMAL_BIRTH_TYPE as dam_birth_type,
b.ANIMAL_BIRTH_WEIGHT as dam_birth_weight,
a.ANIMAL_BIRTH_DATE format=mmddyy10.,
a.ANIMAL_BIRTH_TYPE,
a.ANIMAL_BIRTH_WEIGHT
from
haveParturition as a inner join
haveParturition as b on a.dam=b.animal;
select * from want;
quit;``````
PG

All Replies
Solution
‎12-23-2015 01:24 PM
Posts: 5,543

## Re: Ordering Data from a pedigree - Females and birth order

A classic case of self-join:

``````
data haveParturition;
set have;
by dam sire animal_birth_date notsorted;
if first.dam then Parturition_Order = 0;
if first.animal_birth_date then Parturition_Order + 1;
run;

proc sql;
create table want as
select
a.Parturition_Order,
a.animal as newborn,
a.dam,
a.sire,
a.dam_born_date format=mmddyy10.,
b.ANIMAL_BIRTH_TYPE as dam_birth_type,
b.ANIMAL_BIRTH_WEIGHT as dam_birth_weight,
a.ANIMAL_BIRTH_DATE format=mmddyy10.,
a.ANIMAL_BIRTH_TYPE,
a.ANIMAL_BIRTH_WEIGHT
from
haveParturition as a inner join
haveParturition as b on a.dam=b.animal;
select * from want;
quit;``````
PG
Frequent Contributor
Posts: 115

## Re: Ordering Data from a pedigree - Females and birth order

Can i apply this routine into a big database, i mean, ia have much more females becoming dams .

Super User
Posts: 10,787

## Re: Ordering Data from a pedigree - Females and birth order

If it is a big table , Try Hash Table.
Posts: 5,543

## Re: Ordering Data from a pedigree - Females and birth order

A simple join like this is fairly efficient. It will work well at least up to a million animals . Just make sure all offsprings from a dam are consecutive in the dataset and in chronological order.

PG
Frequent Contributor
Posts: 115

## Re: Ordering Data from a pedigree - Females and birth order

Good day my friend:

Sorry for the delay to answer:

i have tested the routine ou gave me, the output works good but it shows this in the log:

NOTE: There were 9 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.HAVEPARTURITION has 9 observations and 8 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

63
64 proc sql;
65 create table want as
66 select
67 a.Parturition_Order,
68 a.animal as newborn,
69 a.dam,
70 a.sire,
71 a.dam_born_date format=mmddyy10.,
72 b.ANIMAL_BIRTH_TYPE as dam_birth_type,
73 b.ANIMAL_BIRTH_WEIGHT as dam_birth_weight,
74 a.ANIMAL_BIRTH_DATE format=mmddyy10.,
75 a.ANIMAL_BIRTH_TYPE,
76 a.ANIMAL_BIRTH_WEIGHT
77 from
78 haveParturition as a inner join
79 haveParturition as b on a.dam=b.animal;
ERROR: Character expression requires a character format.
ERROR: Character expression requires a character format.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
80 select * from want;
ERROR: File WORK.WANT.DATA does not exist.
81 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

82
83 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
95
Posts: 5,543

## Re: Ordering Data from a pedigree - Females and birth order

I read the dates as SAS dates, not character strings. I suggest you do the same, either when you read the data&colon;

``````data have;
input ANIMAL DAM SIRE DAM_BORN_DATE :mmddyy10.
ANIMAL_BIRTH_DATE :mmddyy10. ANIMAL_BIRTH_TYPE ANIMAL_BIRTH_WEIGHT;
cards;
6561    4597    5982    06/13/2002    08/05/2008    1    4.8
6870    6561    5878    08/05/2008    10/13/2009    1    3.9
6971    6561    6626    08/05/2008    10/27/2010    3    2.3
6972    6561    6626    08/05/2008    10/27/2010    3    1.9
6973    6561    6626    08/05/2008    10/27/2010    3    2.2
7284    6561    6467    08/05/2008    12/21/2011    2    3.6
7285    6561    6467    08/05/2008    12/21/2011    2    3.3
7603    6561    7069    08/05/2008    05/07/2014    2    4
7603.1    6561    7069    08/05/2008    05/07/2014    2    3.2
;
``````

or by transforming the data you already have

``````data have;
set have;
ABD = input(ANIMAL_BIRTH_DATE, mmddyy10.);
DBD = input(DAM_BORN_DATE, mmddyy10.);
drop ANIMAL_BIRTH_DATE DAM_BORN_DATE;
rename ABD=ANIMAL_BIRTH_DATE DBD=DAM_BORN_DATE;
run;``````
PG
Contributor
Posts: 61

## Re: Ordering Data from a pedigree - Females and birth order

Try following code. You can add order latter. Dataset will have to be sorted.

``````data have;
input ANIMAL    DAM    SIRE    DAM_BORN_DATE\$    ANIMAL_BIRTH_DATE\$    ANIMAL_BIRTH_TYPE    ANIMAL_BIRTH_WEIGHT;
cards;
6561    4597    5982    06/13/2002    08/05/2008    1    4.8
6870    6561    5878    08/05/2008    10/13/2009    1    3.9
6971    6561    6626    08/05/2008    10/27/2010    3    2.3
6972    6561    6626    08/05/2008    10/27/2010    3    1.9
6973    6561    6626    08/05/2008    10/27/2010    3    2.2
7284    6561    6467    08/05/2008    12/21/2011    2    3.6
7285    6561    6467    08/05/2008    12/21/2011    2    3.3
7603    6561    7069    08/05/2008    05/07/2014    2    4
7603.1  6561    7069    08/05/2008    05/07/2014    2    3.2
;
run;

data new(rename=(animal=newborn)) ;
merge have(in=a keep=animal dam sire ANIMAL_BIRTH_DATE ANIMAL_BIRTH_TYPE ANIMAL_BIRTH_WEIGHT)
have(in=b keep=animal DAM_BORN_DATE ANIMAL_BIRTH_TYPE ANIMAL_BIRTH_WEIGHT
rename=(animal=dam  ANIMAL_BIRTH_TYPE=DAM_BIRTH_TYPE ANIMAL_BIRTH_WEIGHT=DAM_BIRTH_WEIGHT));
by dam;
if a and b;
run;``````
Frequent Contributor
Posts: 115

## Re: Ordering Data from a pedigree - Females and birth order

PG good night:

Your command is such as perfect as i wanted to work, in the example y put jus one animal, i woul like to ask you for mor help, i added some data&colon;

data have;
input ANIMAL DAM SIRE DAM_BORN_DATE :mmddyy10. ANIMAL_BIRTH_DATE :mmddyy10. ANIMAL_BIRTH_TYPE ANIMAL_BIRTH_WEIGHT;
cards;
5496           3671           4469           09/20/1999           06/08/2005           2           3.1
5497           3671           4469           09/20/1999           06/08/2005           2           3
5498           4539           3297           06/09/2002           06/09/2005           1           3.6
5498.1           4752           .           05/01/2003           06/09/2005           2           2.2
5498.2           4752           .           05/01/2003           06/09/2005           2           1.9
5501           3789           4559           10/20/1999           06/09/2005           2           2.7
5502           3789           4559           10/20/1999           06/09/2005           2           2.6
5502.1           3793           4480           01/14/2000           06/10/2005           2           3.1
5502.2           3793           4480           01/14/2000           06/10/2005           2           3.1
5503           3571           4574           05/25/1999           06/10/2005           2           3.3
5504           3571           4574           05/25/1999           06/10/2005           2           2.5
5505           4073           4559           09/27/2000           06/10/2005           2           2.7
5506           4073           4559           09/27/2000           06/10/2005           2           3.6
6561           4597           5982           06/13/2002           08/05/2008           1           4.8
6870           6561           5878           08/05/2008           10/13/2009           1           3.9
6971           6561           6626           08/05/2008           10/27/2010           3           2.3
6972           6561           6626           08/05/2008           10/27/2010           3           1.9
6973           6561           6626           08/05/2008           10/27/2010           3           2.2
7284           6561           6467           08/05/2008           12/21/2011           2           3.6
7285           6561           6467           08/05/2008           12/21/2011           2           3.3
7603           6561           7069           08/05/2008           05/07/2014           2           4
7603.1           6561           7069           08/05/2008           05/07/2014           2           3.2
;

i hope to have some help and im sorry if im disturbing you, well my knowled about SQL is almost nule.

Thank you very much

Posts: 5,543

## Re: Ordering Data from a pedigree - Females and birth order

If you want to keep all animals in the list, not just the ones with DAM details, replace INNER JOIN with LEFT JOIN :

``````proc sql;
create table want as
select
a.Parturition_Order,
a.animal as newborn,
a.dam,
a.sire,
a.dam_born_date format=mmddyy10.,
b.ANIMAL_BIRTH_TYPE as dam_birth_type,
b.ANIMAL_BIRTH_WEIGHT as dam_birth_weight,
a.ANIMAL_BIRTH_DATE format=mmddyy10.,
a.ANIMAL_BIRTH_TYPE,
a.ANIMAL_BIRTH_WEIGHT
from
haveParturition as a left join
haveParturition as b on a.dam=b.animal
order by newborn;
select * from want;
quit;``````
PG
🔒 This topic is solved and locked.