Proc Report sort across and descending by number

[Q1983]

data have;
format setup_date date9.;
infile datalines;
input setup_date date9. Division $ cnt;
return;
datalines;
1Jun2018 Central 4
10Jun2018 Central 14
11Jun2018 Central 11
1May2018 Central 5
12May2018 Central 15
1Sep2018 Central 13
11Apr2019 Atlantic 13
17Apr2019 Atlantic 33
19Apr2019 Atlantic 3
1Feb2019 Atlantic 11
14Feb2019 Atlantic 21
1Jan2017 Pacific 11
13Jan2017 Pacific 13
14Jan2017 Pacific 14
1Mar2019 Central 11
14Mar2019 Central 13
;run;

data have2;
length Division $25.;
set have;
monthsum = month(setup_date);
year1=year(setup_date);
year2=put(year1,4.);
run;
/*setup_date date9. Division $ cnt monthsum */

proc report data=have2 NOWD Wrap style(summary)=Header;
columns Division year2 cnt monthsum;
define Division /group ;
define year2 /across order=data '';
define monthsum /noprint order = data;
define cnt /sum f=comma6. 'Row Tot';
compute after;
Division='Grand Total';
endcomp;
break after Division / ol summarize skip;
rbreak after /summarize;
run;

1. How can I structure so it displays the Year across by ascending order

2. How can I display the Row total by descending order

[RichardDeVen · Accepted solution]

You will need to precompute the SUM(CNT) over each DIVISION and use that as a / GROUP DESCENDING NOPRINT variable.

The across variable is FORMATted and specified as ORDER=FORMATTED, or ORDER=INTERNAL, no-need for a precomputation that tears apart the setup_date.  Also, no need to precompute a resized DIVISION, a computed variable can perform the same action directly in the REPORT.

Example:

data have;
format setup_date date9.;
infile datalines;
input setup_date date9. Division $ cnt;
return;
datalines;
1Jun2018 Central 4
10Jun2018 Central 14
11Jun2018 Central 11
1May2018 Central 5
12May2018 Central 15
1Sep2018 Central 13
11Apr2019 Atlantic 13
17Apr2019 Atlantic 33
19Apr2019 Atlantic 3
1Feb2019 Atlantic 11
14Feb2019 Atlantic 21
1Jan2017 Pacific 11
13Jan2017 Pacific 13
14Jan2017 Pacific 14
1Mar2019 Central 11
14Mar2019 Central 13
;run;

proc sql;
  create table report_data as
  select *,
    sum(cnt) as div_cnt_sum /* precompute division level CNT sum for ordering rows */
  from have 
  group by division;

ods html file='report.html' style=plateau;

proc report data=report_data wrap style(summary)=Header;
  columns div_cnt_sum Division WiderDivision setup_date cnt ;

  define div_cnt_sum / group descending noprint ;                      /* orders the rows and is a 1x (i.e. non-impacting group) on Row Tot */
  define Division / group noprint;                                     /* groups for Row Tot */
  define WiderDivision / 'Division' computed;                          /* in REPORT wider division for GRAND TOTAL */ 
  define setup_date / across format=year4. order=internal '';          /* format across variable to show (and summarize group for N-count) based on only 4-digit year portion of setup_date */
  define cnt / sum f=comma6. 'Row Tot';                                /* report computed sum will match div_cnt_sum precomputed for row ordering */

  break after Division / summarize;
  rbreak after / summarize;

  compute WiderDivision / character length=25;                            /* specify the widerness */
    WiderDivision = Division;
  endcomp;

  compute after;
    WiderDivision='Grand Total';
  endcomp;
run;

ods html close;

Output

[Q1983]

Thanks this worked. Lets say I have one more variable to add. Lets say Officer. How would the code change incorporate Officer. In this case it should be sorted descending using Division as the class and sorted in descending by Officer. See the detail here.
setup_date Division Officer cnt
17Apr2019 Atlantic James 33
14Feb2019 Atlantic Shane 21
11Apr2019 Atlantic James 13
01Feb2019 Atlantic Doglass 11
19Apr2019 Atlantic Smith 3
12May2018 Central Stone 15
12May2018 Central Grant 15
10Jun2018 Central Smith 14
12May2018 Central Grant 14
01Sep2018 Central Jones 13
11Jun2018 Central Shelton 11
01May2018 Central Baldwin 5
01Jun2018 Central Smith 4