Renaming months by grouping in seasons

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 103
Accepted Solution

Renaming months by grouping in seasons

Good morning SAS friends:

 

I have this data set containing information about months of the year;

 

data have;

input year    month;

cards;
1990    January
1990    February
1990    March
1990    April
1990    May
1990    June
1990    July
1990    August
1990    September
1990    October
;

 

With this data, i need to clasify the seasons belonging to each month, IN GROUPS, then the GROUP number 1 correspond to the months of :January, February and March; the GROUP  number 2 correspond to April, May and June; the GROUP number 3 to the months of July, August and September and the GROUP number 4 will be formed for  October, November and December.

 

Th result is as follows:

 

year month GROUP
1990 January 1
1990 February 1
1990 March 1
1990 April 2
1990 May 2
1990 June 2
1990 July 3
1990 August 3
1990 September 3
1990 October 4

 

Thank you very much


Accepted Solutions
Solution
‎01-26-2016 09:24 AM
Esteemed Advisor
Esteemed Advisor
Posts: 7,190

Re: Renaming months by grouping in seasons

Alternatively:

data have;
  input year $ month $;
  select(substr(month,1,3));
    when('Jan','Feb','Mar') group=1;
    when('Apr','May','Jun') group=2;
    when('Jul','Aug','Sep') group=3;
    otherwise group=4;
  end;
cards;
1990    January
1990    February
1990    March
1990    April
1990    May
1990    June
1990    July
1990    August
1990    September
1990    October
;
run;

View solution in original post


All Replies
Grand Advisor
Posts: 10,196

Re: Renaming months by grouping in seasons

Two solutions involving FORMAT,
first a custom format
proc format; value $MonthGroup 'January'='1' 'February'='1' 'March'='1' 'April'='2' 'May'='2' 'June'='2' 'July'='3' 'August'='3' 'September'='3' 'October'='4' ; run; You can then either create a new variable in your data step with Group= put(month,$MonthGroup.)

Or don't bother adding the variable but use the format when displaying or analyzing;

 

proc freq data=have;

   tables month;

   format month $MonthGroup.;

run;

 

Or use the YEAR and Month information to create a SAS Date valued variable and then use the SAS supplied QTR. format on an actual date since your "group" coincides with the calendar quarter.

Solution
‎01-26-2016 09:24 AM
Esteemed Advisor
Esteemed Advisor
Posts: 7,190

Re: Renaming months by grouping in seasons

Alternatively:

data have;
  input year $ month $;
  select(substr(month,1,3));
    when('Jan','Feb','Mar') group=1;
    when('Apr','May','Jun') group=2;
    when('Jul','Aug','Sep') group=3;
    otherwise group=4;
  end;
cards;
1990    January
1990    February
1990    March
1990    April
1990    May
1990    June
1990    July
1990    August
1990    September
1990    October
;
run;
Frequent Contributor
Posts: 103

Re: Renaming months by grouping in seasons

Good Morning:

 

I have accepted this answer as solution because this command is very usefull to make different type of combination for sub - grouping months.

 

Thank you very much

Frequent Contributor
Posts: 103

Re: Renaming months by grouping in seasons

[ Edited ]

Hi there:

 

i will explain the situation, and of course is linked to your command (given by answer):

 

i have an original dataset formated like that:

 

data have;
input     ANIMAL  ANIMAL_BIRTH_DATE:mmddyy.   ;
format animal_birth_date yymmdd10.;

cards;
81    07/02/07
271    08/26/07
533    06/02/01
683    09/10/99
739    12/09/99
782    10/04/01
1515    12/01/07
1533            .
2555            .
3041            .
3106            .
3109    06/15/97
3132    11/19/97
3136    11/19/97
3280    05/01/98;
;

 

Here i needed to split the format of the animal_birth_date and i added this command:

 

data have;
input     ANIMAL  ANIMAL_BIRTH_DATE:mmddyy.   ;
format animal_birth_date yymmdd10.;

 

Day_of_Week_ABD=put(weekday(animal_birth_date)+1,downame10.);
year_ABD=year(animal_birth_date);
month_ABD=put(animal_birth_date,monname10.);
day_ABD=day(animal_birth_date);

 

cards;
81    07/02/07
271    08/26/07
533    06/02/01
683    09/10/99
739    12/09/99
782    10/04/01
1515    12/01/07
1533            .
2555            .
3041            .
3106            .
3109    06/15/97
3132    11/19/97
3136    11/19/97
3280    05/01/98
;

 

This was to separate the month, day and year into respective columns,

 

here is where i need to apply the command you sent to me making this:

 

data have;
set have;
  select(substr(month_ABD,1,3));
    when('May','Jun','Jul','Aug','Sep','Oct') CG_MONTH=1;
    when('Nov','Dec','Jan','Feb','Mar','Apr') CG_MONTH=2;
    when('.') group='.';
    end;
run;

 

Running this i obtain this erro:

 

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
55
56 data have;
57 set have;
58 select(substr(month_ABD,1,3));
59 when('May','Jun','Jul','Aug','Sep','Oct') CG_MONTH=1;
60 when('Nov','Dec','Jan','Feb','Mar','Apr') CG_MONTH=2;
61 when(' ') group='.';
62 end;
63 run;
 
ERROR: Unsatisfied WHEN clause and no OTHERWISE clause at line 62 column 5.
ANIMAL=683 ANIMAL_BIRTH_DATE=1999-09-10 Day_of_Week_ABD=Friday year_ABD=1999 month_ABD=September day_ABD=10 CG_MONTH=. group=
_ERROR_=1 _N_=4
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 4 observations read from the data set WORK.HAVE.
WARNING: The data set WORK.HAVE may be incomplete. When this step was stopped there were 3 observations and 8 variables.
WARNING: Data set WORK.HAVE was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
 
So i need to read al data set, including missing values,
 
Thank you very much

 

Esteemed Advisor
Esteemed Advisor
Posts: 7,190

Re: Renaming months by grouping in seasons

Well @Ksharp has given you your answer.  The clue is in the log:

ERROR: Unsatisfied WHEN clause and no OTHERWISE clause at line 62 column 5.

 

Trusted Advisor
Posts: 1,114

Re: Renaming months by grouping in seasons

Have you considered using a numeric variable MONTH (with values 1, ..., 12)? At least, this is suggested by your INPUT statement.

 

You could then simply define

 

group=int((month-1)/3)+1;

 

 

The numeric month values would come handy in other situations as well. Examples:

  • if month in (1,4,7,10)
  • where 3<=month<=9
  • date=mdy(month,1,2016)
  • sorting by month or by year month

If you need the month names (or their abbreviations) for reporting, you could create them easily

monthname=put(28*month, monname.-l);
monthabbr=put(28*month, monname3.);

or format MONTH directly with a user-defined format.

 

Grand Advisor
Posts: 9,567

Re: Renaming months by grouping in seasons

Use function QTR().

 

data have;
  input year $ month : $20.;
 qtr=qtr(input(cats('01',month,year),anydtdte32.));
cards;
1990    January
1990    February
1990    March
1990    April
1990    May
1990    June
1990    July
1990    August
1990    September
1990    October
;
run;
Frequent Contributor
Posts: 103

Re: Renaming months by grouping in seasons

good night:

 

how could you use QTR gfunction here:

 

data have;
input     ANIMAL  ANIMAL_BIRTH_DATE:mmddyy.   ;
format animal_birth_date yymmdd10.;

Day_of_Week_ABD=put(weekday(animal_birth_date)+1,downame10.);
year_ABD=year(animal_birth_date);
month_ABD=put(animal_birth_date,monname10.);
day_ABD=day(animal_birth_date);


cards;
81    07/02/07
271    08/26/07
533    06/02/01
683    09/10/99
739    12/09/99
782    10/04/01
1515    12/01/07
1533            .
2555            .
3041            .
3106            .
3109    06/15/97
3132    11/19/97
3136    11/19/97
3280    05/01/98
;

 

Thank you

Grand Advisor
Posts: 9,567

Re: Renaming months by grouping in seasons

It would be easy if there were already a DATE type variable. But you did not post the output you want.

 

 

data have;
input ANIMAL ANIMAL_BIRTH_DATE:mmddyy. ;
format animal_birth_date yymmdd10.;

Day_of_Week_ABD=put(weekday(animal_birth_date)+1,downame10.);
year_ABD=year(animal_birth_date);
month_ABD=put(animal_birth_date,monname10.);
day_ABD=day(animal_birth_date);

qtr=qtr(animal_birth_date);

cards;
81 07/02/07
271 08/26/07
533 06/02/01
683 09/10/99
739 12/09/99
782 10/04/01
1515 12/01/07
1533 .
2555 .
3041 .
3106 .
3109 06/15/97
3132 11/19/97
3136 11/19/97
3280 05/01/98
;

Frequent Contributor
Posts: 103

Re: Renaming months by grouping in seasons

Thanks for your colaboration:

 

Using this dataset:

 

data have;
input ANIMAL ANIMAL_BIRTH_DATE:mmddyy. ;
format animal_birth_date yymmdd10.;

Day_of_Week_ABD=put(weekday(animal_birth_date)+1,downame10.);
year_ABD=year(animal_birth_date);
month_ABD=put(animal_birth_date,monname10.);
day_ABD=day(animal_birth_date);

qtr=qtr(animal_birth_date);

cards;
81 07/02/07
271 08/26/07
533 06/02/01
683 09/10/99
739 12/09/99
782 10/04/01
1515 12/01/07
1533 .
2555 .
3041 .
3106 .
3109 06/15/97
3132 11/19/97
3136 11/19/97
3280 05/01/98
;

 

I need to form 1 (one) new column (CG_MONTH) containing the information linked to the month

    when('May','Jun','Jul','Aug','Sep','Oct') CG_MONTH=1;
    when('Nov','Dec','Jan','Feb','Mar','Apr') CG_MONTH=2;

 

thank you very much

Grand Advisor
Posts: 9,567

Re: Renaming months by grouping in seasons

data have;
set have;
select(substr(month_ABD,1,3));
when('May','Jun','Jul','Aug','Sep','Oct') CG_MONTH=1;
when('Nov','Dec','Jan','Feb','Mar','Apr') CG_MONTH=2;
when('.') group='.';
otherwise;
end;
run;

☑ This topic is SOLVED.

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

Discussion stats
  • 11 replies
  • 543 views
  • 2 likes
  • 5 in conversation