DATA Step, Macro, Functions and more

Sorting by month but starting in the middle of the year

Reply
Occasional Contributor
Posts: 7

Sorting by month but starting in the middle of the year

I'm working on a basic data set for a school project and I'm having problems with sorting and formatting.  SAS 9.4

 

There are three variables:  Month; Moon; and Admission.  There's a year's worth of data, with each month having three observations based on the moon:  Before; During; and After.  

 

However, the months start in August and not in January, but I still need to order everything chronologically.  I know I have to do something with formats and labels but I keep getting an error where it will only list August, or it will list all of the months, but in alphabetical order.  I thought it would work where I convert the three letter months into numbers and then change them back with labels after sorting.

 

I've spent over two hours trying to figure this out and I'm at my wit's end.  It's especially frustrating because this is just for the basic descriptive statistics portion of the project.  Here's what I have...PLEASE HELP!

 

proc format data;
Value $Month Aug=1 Sep=2 Oct=3 Nov=4 Dec=5 Jan=6 Feb=7 Mar=8 Apr=9 May=10 Jun=11 Jul=12;
run;

 

data MentalHealth;
Input Month $ Moon $ Admission;
Datalines;
Aug Before 6.4
Sep Before 7.1
Oct Before 6.5
Nov Before 8.6
Dec Before 8.1
Jan Before 10.4
Feb Before 11.5
Mar Before 13.8
Apr Before 15.4
May Before 15.7
Jun Before 11.7
Jul Before 15.8
Aug During 5
Sep During 13
Oct During 14
Nov During 12
Dec During 6
Jan During 9
Feb During 13
Mar During 16
Apr During 25
May During 14
Jun During 14
Jul During 20
Aug After 5.8
Sep After 9.2
Oct After 7.9
Nov After 7.7
Dec After 11
Jan After 12.9
Feb After 13.5
Mar After 13.1
Apr After 15.8
May After 13.3
Jun After 12.8
Jul After 14.5
;

proc sort data=MentalHealth;
By Month;
run;


proc means data=MentalHealth;
Title "Descriptive Statistics by Month";
Var Admission;
By Month;
Label 1="August" 2="September" 3="October" 4="November" 5="December" 6="January" 7="February" 8="March" 9="April" 10="May" 11="June" 12="July";
run;

Super User
Posts: 22,873

Re: Sorting by month but starting in the middle of the year

Labels are used for variable names, formats are used for variable labels.

 

You're on the right track to use temp variables for your months to order them, but I would suggest adding a year as well so it sorts correctly because you don't want July 2015 Before August 2016 or something like that. 

 

I see you created a format statement but you never used it anywhere in your code either. Rather than roll your own though I would probably just create a SAS date and use that with the MONNAME3 format to show the date. 

 

untested code:

 

data MentalHealth;
Input Month $ Moon $ Admission;
date9_var = catt('01', month, '2016');
month_num=input(date9_var, date9.);
if month(month_num) <= 7 then month_num=intnx('year', month_num, 1, 'b');
format month_num date9.;
Datalines;
Aug Before 6.4
Sep Before 7.1
Oct Before 6.5
Nov Before 8.6
Dec Before 8.1
Jan Before 10.4
Feb Before 11.5
Mar Before 13.8
Apr Before 15.4
May Before 15.7
Jun Before 11.7
Jul Before 15.8
Aug During 5
Sep During 13
Oct During 14
Nov During 12
Dec During 6
Jan During 9
Feb During 13
Mar During 16
Apr During 25
May During 14
Jun During 14
Jul During 20
Aug After 5.8
Sep After 9.2
Oct After 7.9
Nov After 7.7
Dec After 11
Jan After 12.9
Feb After 13.5
Mar After 13.1
Apr After 15.8
May After 13.3
Jun After 12.8
Jul After 14.5
;
run;

proc sort data=MentalHealth; by month_num; run;

proc means data=MentalHealth;
Title "Descriptive Statistics by Month";
Var Admission;
class Month_NUM;
run;

 

Spoiler

phuzface wrote:

I'm working on a basic data set for a school project and I'm having problems with sorting and formatting.  SAS 9.4

 

There are three variables:  Month; Moon; and Admission.  There's a year's worth of data, with each month having three observations based on the moon:  Before; During; and After.  

 

However, the months start in August and not in January, but I still need to order everything chronologically.  I know I have to do something with formats and labels but I keep getting an error where it will only list August, or it will list all of the months, but in alphabetical order.  I thought it would work where I convert the three letter months into numbers and then change them back with labels after sorting.

 

I've spent over two hours trying to figure this out and I'm at my wit's end.  It's especially frustrating because this is just for the basic descriptive statistics portion of the project.  Here's what I have...PLEASE HELP!

 

proc format data;
Value $Month Aug=1 Sep=2 Oct=3 Nov=4 Dec=5 Jan=6 Feb=7 Mar=8 Apr=9 May=10 Jun=11 Jul=12;
run;

 

data MentalHealth;
Input Month $ Moon $ Admission;
Datalines;
Aug Before 6.4
Sep Before 7.1
Oct Before 6.5
Nov Before 8.6
Dec Before 8.1
Jan Before 10.4
Feb Before 11.5
Mar Before 13.8
Apr Before 15.4
May Before 15.7
Jun Before 11.7
Jul Before 15.8
Aug During 5
Sep During 13
Oct During 14
Nov During 12
Dec During 6
Jan During 9
Feb During 13
Mar During 16
Apr During 25
May During 14
Jun During 14
Jul During 20
Aug After 5.8
Sep After 9.2
Oct After 7.9
Nov After 7.7
Dec After 11
Jan After 12.9
Feb After 13.5
Mar After 13.1
Apr After 15.8
May After 13.3
Jun After 12.8
Jul After 14.5
;

proc sort data=MentalHealth;
By Month;
run;


proc means data=MentalHealth;
Title "Descriptive Statistics by Month";
Var Admission;
By Month;
Label 1="August" 2="September" 3="October" 4="November" 5="December" 6="January" 7="February" 8="March" 9="April" 10="May" 11="June" 12="July";
run;


 

Esteemed Advisor
Posts: 5,402

Re: Sorting by month but starting in the middle of the year

Use an informat for reading, and a format for reporting:

 

proc format;
inValue myMonth Aug=1 Sep=2 Oct=3 Nov=4 Dec=5 Jan=6 Feb=7 Mar=8 Apr=9 May=10 Jun=11 Jul=12;
value myMonthStr 1="August" 2="September" 3="October" 4="November" 5="December"
6="January" 7="February" 8="March" 9="April" 10="May" 11="June" 12="July";
run;

 

data MentalHealth;
Input Month :myMonth. Moon $ Admission;
Datalines;
Aug Before 6.4
Sep Before 7.1
Oct Before 6.5
Nov Before 8.6
Dec Before 8.1
Jan Before 10.4
Feb Before 11.5
Mar Before 13.8
Apr Before 15.4
May Before 15.7
Jun Before 11.7
Jul Before 15.8
Aug During 5
Sep During 13
Oct During 14
Nov During 12
Dec During 6
Jan During 9
Feb During 13
Mar During 16
Apr During 25
May During 14
Jun During 14
Jul During 20
Aug After 5.8
Sep After 9.2
Oct After 7.9
Nov After 7.7
Dec After 11
Jan After 12.9
Feb After 13.5
Mar After 13.1
Apr After 15.8
May After 13.3
Jun After 12.8
Jul After 14.5
;

proc means data=MentalHealth order=unformatted;
Title "Descriptive Statistics by Month";
Var Admission;
class Month;
format month myMonthStr.;
run;
PG
Ask a Question
Discussion stats
  • 2 replies
  • 166 views
  • 2 likes
  • 3 in conversation