Create new variables grouping data

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 103
Accepted Solution

Create new variables grouping data

Good morning SAS colleages:

 

Here is my first 2016 deal (LOL)

 

Well, to work in genetic modelling and to create "Contemporary Groups" we need to merge some groups of variables, this to increase de efficience of the analisy and obtain better explained variance from some fixed effects. Thus, to undertand this exercise i need your help to solve this problem:

 

Here is the initial dataset:

 

LAMBING_ORDER ANIMAL SEX ANIMAL_BIRTH_DATE ANIMAL_MONTH_DATE ANIMAL_DAY_DATE ANIMAL_YEAR_DATE ANIMAL_BIRTH_TYPE
1 6870 M 10/13/2009 10 13 2009 1
2 6971 F 10/27/2010 10 27 2010 3
2 6972 F 10/27/2010 10 27 2010 3
2 6973 M 10/27/2010 10 27 2010 3
3 7284 F 12/21/2011 12 21 2011 2
3 7285 M 12/21/2011 12 21 2011 2
4 7603 M 05/07/2014 5 7 2014 2
4 7603.1 F 05/07/2014 5 7 2014 2

 

SAS command:

 

data have;
input LAMBING_ORDER    ANIMAL    SEX    ANIMAL_BIRTH_DATE:mmddyy10.    ANIMAL_MONTH_DATE    ANIMAL_DAY_DATE    ANIMAL_YEAR_DATE    ANIMAL_BIRTH_TYPE;
cards;
1    6870    M    10/13/2009    10    13    2009    1
2    6971    F    10/27/2010    10    27    2010    3
2    6972    F    10/27/2010    10    27    2010    3
2    6973    M    10/27/2010    10    27    2010    3
3    7284    F    12/21/2011    12    21    2011    2
3    7285    M    12/21/2011    12    21    2011    2
4    7603    M    05/07/2014    5    7    2014    2
4    7603.1    F    05/07/2014    5    7    2014    2
;

 

As you can see, the birth date was separate in 3 columns, each with information about the day, month and year from the animal birth, this is for helping the grouping model:

 

so, the trouble is, i need to create a new colum where: the animal born in the year "X", in the month "Y", with the sex "M" and the type of birth "Z" are constituing a new variable called CONTEMP_GROUP, and the value for this is 1

 

In the example above this would result like:

 

LAMBING_ORDER ANIMAL SEX ANIMAL_BIRTH_DATE ANIMAL_MONTH_BIRTH ANIMAL_DAY_BIRTH ANIMAL_YEAR_BIRTH ANIMAL_BIRTH_TYPE CONTEMP_GROUP
1 6870 M 10/13/2009 10 13 2009 1 1
2 6971 F 10/27/2010 10 27 2010 3 2
2 6972 F 10/27/2010 10 27 2010 3 2
2 6973 M 10/27/2010 10 27 2010 3 3
3 7284 F 12/21/2011 12 21 2011 2 4
3 7285 M 12/21/2011 12 21 2011 2 5
4 7603 M 05/07/2014 5 7 2014 2 6
4 7603.1 F 05/07/2014 5 7 2014 2 7

 

this is so ilustrative to complete to merge all the variables into just one.

 

i hope to get your answers and sugestions.

 

Thanks


Accepted Solutions
Solution
‎01-19-2016 09:27 AM
Super User
Posts: 18,997

Re: Create new variables grouping data

It sounds like a BY group count, once your data is sorted properly.

 

I'm assuming it is here, but I added the NOTSORTED option anyways.

 

data want;
set have;
by ANIMAL_YEAR_DATE ANIMAL_MONTH_DATE SEX ANIMAL_BIRTH_TYPE NOTSORTED;

RETAIN GROUP 0;

if first.ANIMAL_BIRTH_TYPE then GROUP+1;

RUN;

View solution in original post


All Replies
Solution
‎01-19-2016 09:27 AM
Super User
Posts: 18,997

Re: Create new variables grouping data

It sounds like a BY group count, once your data is sorted properly.

 

I'm assuming it is here, but I added the NOTSORTED option anyways.

 

data want;
set have;
by ANIMAL_YEAR_DATE ANIMAL_MONTH_DATE SEX ANIMAL_BIRTH_TYPE NOTSORTED;

RETAIN GROUP 0;

if first.ANIMAL_BIRTH_TYPE then GROUP+1;

RUN;
Frequent Contributor
Posts: 103

Re: Create new variables grouping data

Hi Reeza:

 

I Know this could be a very easy question....and this is:

 

I need to SEE the label of the variable: GROUP

 

as

 

'Contemponary Genetic Group'

 

Thank you very much

Super User
Posts: 18,997

Re: Create new variables grouping data

Label group='my label text';
Frequent Contributor
Posts: 103

Re: Create new variables grouping data

[ Edited ]
data want;
set have;
by ANIMAL_YEAR_DATE ANIMAL_MONTH_DATE SEX ANIMAL_BIRTH_TYPE NOTSORTED;
RETAIN GROUP 0;
if first.ANIMAL_BIRTH_TYPE then GROUP+1;
Label group='my label text';
RUN;




Is not working properly
Super User
Posts: 18,997

Re: Create new variables grouping data

What does not working properly mean?
Frequent Contributor
Posts: 103

Re: Create new variables grouping data

the statement:

 

ata want;
set have;
by ANIMAL_YEAR_DATE ANIMAL_MONTH_DATE SEX ANIMAL_BIRTH_TYPE NOTSORTED;
RETAIN GROUP 0;
if first.ANIMAL_BIRTH_TYPE then GROUP+1;Label group='my label text'; 
RUN

 

does not work properly, means that "group"  label is not changing in the answer

 

Thank you very much

Super User
Posts: 18,997

Re: Create new variables grouping data

Post the results from a proc contents.

Also, what do you mean by label, lets make sure were talking about the same thing.
Frequent Contributor
Posts: 103

Re: Create new variables grouping data

Good Day Reeza, thank for your suport:

 

Here is the full command:

 

data have;
input LAMBING_ORDER    ANIMAL    SEX$    ANIMAL_BIRTH_DATE:mmddyy10.    month_ABD    day_ABD    year_ABD    ANIMAL_BIRTH_TYPE;
format animal_birth_date yymmdd10.;
cards;
1    6870    M    10/13/2009    10    13    2009    1
2    6971    F    10/27/2010    10    27    2010    3
2    6972    F    10/27/2010    10    27    2010    3
2    6973    M    10/27/2010    10    27    2010    3
3    7284    F    12/21/2011    12    21    2011    2
3    7285    M    12/21/2011    12    21    2011    2
4    7603    M    05/07/2014    5      7    2014    2
4    7603.1  F    05/07/2014    5      7    2014    2
;

data want;
set have;
by year_ABD month_ABD SEX ANIMAL_BIRTH_TYPE NOTSORTED;
label group = 'my label here';
RETAIN group 0;
if first.ANIMAL_BIRTH_TYPE then group+1;
RUN;

proc print data = want style(column) = [just=center];
run;

 

the result is:

 

Obs LAMBING_ORDER ANIMAL SEX ANIMAL_BIRTH_DATE month_ABD day_ABD year_ABD ANIMAL_BIRTH_TYPE group
1 1 6870 M 2009-10-13 10 13 2009 1 1
2 2 6971 F 2010-10-27 10 27 2010 3 2
3 2 6972 F 2010-10-27 10 27 2010 3 2
4 2 6973 M 2010-10-27 10 27 2010 3 3
5 3 7284 F 2011-12-21 12 21 2011 2 4
6 3 7285 M 2011-12-21 12 21 2011 2 5
7 4 7603 M 2014-05-07 5 7 2014 2 6
8 4 7603.1 F 2014-05-07 5 7 2014 2 7
Super User
Posts: 18,997

Re: Create new variables grouping data

Add the label option to proc print statement. Proc print does not display labels by default.
Frequent Contributor
Posts: 103

Re: Create new variables grouping data

Thank uou Rezza:

 

im trying this:

 

proc print data = want style(column) = [just=center];
label group = 'my label here';
run;

 

but still not geting the result

 

 

Super User
Posts: 18,997

Re: Create new variables grouping data

Add the word label after data=want
☑ This topic is solved.

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

Discussion stats
  • 11 replies
  • 600 views
  • 1 like
  • 2 in conversation