proc report month sorting issue

[Q1983]

data test1;

infile datalines;

input setup_date date9. Division $ cnt;

return;

datalines;

1Jun2018 Central 4

1May2018 Central 5

1Apr2019 Atlantic 3

1Feb2019 Atlantic 11

1Jan2017 Pacific 11

1Mar2019 Central 11

;run;

data test2;

set test1;

format setup_date date9.;

run;

/*mydata is the data i start with (queried from the database)

I am just showing test1 and 2 because this is a created example*/

data mydata;

set test2;

year1 = year(setup_date);/*returns numeric*/

year2 = put(year1,4.);/*change to character*/

Month1 = put(setup_date,monname3.);/*change to character*/

run;

proc report data = mydata nowd style(summary)=Header;

column year2 Month1 cnt rtot;

define year2 /group ;

define Month1 /group ;

define cnt /sum f=comma6.;

define RTot /computed f=comma16. "Row Totals";

compute RTot;

RTot=sum(cnt.sum);/*row_tot*/

endcomp;

compute before;

sum=cnt.sum;

endcomp;

compute after;

year2='Grand Total';

endcomp;

break after year2 / ol summarize skip;/*total by group*/

rbreak after /summarize;

run;

/*if you run the code the year2 is in correct order however the month is in alpha order and not by month

How can I fix this?*/

[Cynthia_sas · Accepted solution]

Hi:

  The character variable for month is sorting as I would expect. Consider these changes to your code:

Which produces the correct order for MONTH1:

but only because the MONTHNUM variable is added to the report as a numeric variable.

Hope this helps,

Cynthia

[ballardw]

Don't even need a month number variable the original SETUP_date works just fine with the proper format and order option.

proc report data = mydata nowd style(summary)=Header;
column year2 setup_date cnt rtot;
define year2 /group ;
define setup_date /group f=monname3. order=internal ;
define cnt /sum f=comma6.;
define RTot /computed f=comma16. "Row Totals";
compute RTot; 
RTot=sum(cnt.sum);/*row_tot*/
endcomp;
compute before;
 
sum=cnt.sum;
endcomp;
compute after;
 year2='Grand Total';
endcomp;
break after year2 / ol summarize skip;/*total by group*/
rbreak after /summarize;
run;

[Cynthia_sas]

Clever approach! I figured the original poster had some other need for character variables. The way they were calculating totals was a bit odd too and not the say I would have done that part of the report either.
Cynthia

[ballardw]

---

@Cynthia_sas wrote:
Clever approach! I figured the original poster had some other need for character variables. The way they were calculating totals was a bit odd too and not the say I would have done that part of the report either.
Cynthia

---

I didn't bother to look at the body for the other columns, just enough to see that my values matched yours.

I just wanted to emphasize the date + format is a powerful grouping tool.