## Creating a new Variable - Grouping columns by an interaction

Solved
Frequent Contributor
Posts: 115

# Creating a new Variable - Grouping columns by an interaction

Good night SAS friends:

Continuing the last post, i want to ask for some help to solve this problem, the data set is as follows:

data have;
input LAMBING_ORDER    ANIMAL     SEX\$    DAM   SIRE ANIMAL_BIRTH_DATE:mmddyy10.    month_ABD    day_ABD    year_ABD    ANIMAL_BIRTH_TYPE;
format animal_birth_date yymmdd10.;
cards;
1    6870    M  1   2    10/13/2009    10    13    2009    1
1    5555    F  3   4    10/14/2009    10    14    2009    1
1    5556    M  5   6    10/14/2009    10    14    2009    2
1    5557    M  5   6    10/14/2009    10    14    2009    2
1    5558    M  7   8    10/15/2009    10    15    2009    2
1    5559    F  9  10    10/15/2009    10    15    2009    2
1    5560    F  9  10    10/15/2009    10    15    2009    2
1    5561    F  11 10    10/15/2009    10    15    2009    2
1    5562    F  11 10    10/15/2009    10    15    2009    2
1    5563    M  12 10    10/16/2009    10    14    2009    1
1    5564    F  12 10    10/16/2009    10    14    2009    1
2    6971    F  12 10    10/27/2010    10    13    2010    3
2    6972    F  12 10    10/27/2010    10    13    2010    3
2    6973    M  12 10    10/27/2010    10    13    2010    3
;

Using the informarion of SEX and birth_type, its needed to create a new variable associated to each animal considering the interaction of birth type with lamb sex resulting this vaues:

1 - one male lamb (this means: birth_type = 1 and sex = M)

2 - one female lamb, (this means: birth_type = 1 and sex = F)

3 - two male lambs, (this means: birth_type = 2 and sex = M, from the same DAM)

4 - two females lambs, (this means: birth_type = 2 and sex = F, from the same DAM)

5 - one male lamb and one female lamb (this means: birth_type = 2 and sex = M and F, from the same DAM)

6 - more than two lambs, independent of sex (this means: birth_type = > 3 )

Thank you very much for your help.

Regards

Accepted Solutions
Solution
‎01-20-2016 06:48 AM
Super User
Posts: 10,784

## Re: Creating a new Variable - Grouping columns by an interaction

Posted in reply to jonatan_velarde

You didn't post the output you need yet ? Assuming I understand what you mean.

``````data have;
input LAMBING_ORDER    ANIMAL     SEX\$    DAM   SIRE ANIMAL_BIRTH_DATE:mmddyy10.    month_ABD    day_ABD    year_ABD    ANIMAL_BIRTH_TYPE;
format animal_birth_date yymmdd10.;
cards;
1    6870    M  1   2    10/13/2009    10    13    2009    1
1    5555    F  3   4    10/14/2009    10    14    2009    1
1    5556    M  5   6    10/14/2009    10    14    2009    2
1    5557    M  5   6    10/14/2009    10    14    2009    2
1    5558    M  7   8    10/15/2009    10    15    2009    2
1    5559    F  9  10    10/15/2009    10    15    2009    2
1    5560    F  9  10    10/15/2009    10    15    2009    2
1    5561    F  11 10    10/15/2009    10    15    2009    2
1    5562    F  11 10    10/15/2009    10    15    2009    2
1    5563    M  12 10    10/16/2009    10    14    2009    1
1    5564    F  12 10    10/16/2009    10    14    2009    1
2    6971    F  12 10    10/27/2010    10    13    2010    3
2    6972    F  12 10    10/27/2010    10    13    2010    3
2    6973    M  12 10    10/27/2010    10    13    2010    3
;
run;
data want;
length flag \$ 40;
flag='more than two lambs';
do until(last.DAM);
set have;
by ANIMAL_BIRTH_TYPE DAM notsorted;
if sex = 'M' then has_M=1;
if sex = 'F' then has_F=1;
end;

do until(last.DAM);
set have;
by ANIMAL_BIRTH_TYPE DAM notsorted;
if ANIMAL_BIRTH_TYPE = 1 and sex = 'M' then flag='one male lamb';
if ANIMAL_BIRTH_TYPE = 1 and sex = 'F' then flag='one female lamb';
if ANIMAL_BIRTH_TYPE = 2 and has_M and not has_F then flag='two male lambs';
if ANIMAL_BIRTH_TYPE = 2 and has_F and not has_M then flag='two females lambs';
if ANIMAL_BIRTH_TYPE = 2 and has_M and has_F then flag='one male lamb and one female lamb';
output;
end;
drop has_:;
run;
``````

All Replies
Solution
‎01-20-2016 06:48 AM
Super User
Posts: 10,784

## Re: Creating a new Variable - Grouping columns by an interaction

Posted in reply to jonatan_velarde

You didn't post the output you need yet ? Assuming I understand what you mean.

``````data have;
input LAMBING_ORDER    ANIMAL     SEX\$    DAM   SIRE ANIMAL_BIRTH_DATE:mmddyy10.    month_ABD    day_ABD    year_ABD    ANIMAL_BIRTH_TYPE;
format animal_birth_date yymmdd10.;
cards;
1    6870    M  1   2    10/13/2009    10    13    2009    1
1    5555    F  3   4    10/14/2009    10    14    2009    1
1    5556    M  5   6    10/14/2009    10    14    2009    2
1    5557    M  5   6    10/14/2009    10    14    2009    2
1    5558    M  7   8    10/15/2009    10    15    2009    2
1    5559    F  9  10    10/15/2009    10    15    2009    2
1    5560    F  9  10    10/15/2009    10    15    2009    2
1    5561    F  11 10    10/15/2009    10    15    2009    2
1    5562    F  11 10    10/15/2009    10    15    2009    2
1    5563    M  12 10    10/16/2009    10    14    2009    1
1    5564    F  12 10    10/16/2009    10    14    2009    1
2    6971    F  12 10    10/27/2010    10    13    2010    3
2    6972    F  12 10    10/27/2010    10    13    2010    3
2    6973    M  12 10    10/27/2010    10    13    2010    3
;
run;
data want;
length flag \$ 40;
flag='more than two lambs';
do until(last.DAM);
set have;
by ANIMAL_BIRTH_TYPE DAM notsorted;
if sex = 'M' then has_M=1;
if sex = 'F' then has_F=1;
end;

do until(last.DAM);
set have;
by ANIMAL_BIRTH_TYPE DAM notsorted;
if ANIMAL_BIRTH_TYPE = 1 and sex = 'M' then flag='one male lamb';
if ANIMAL_BIRTH_TYPE = 1 and sex = 'F' then flag='one female lamb';
if ANIMAL_BIRTH_TYPE = 2 and has_M and not has_F then flag='two male lambs';
if ANIMAL_BIRTH_TYPE = 2 and has_F and not has_M then flag='two females lambs';
if ANIMAL_BIRTH_TYPE = 2 and has_M and has_F then flag='one male lamb and one female lamb';
output;
end;
drop has_:;
run;
``````
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 1 reply
• 306 views
• 0 likes
• 2 in conversation