Proc report not ordering values?

[ja_lew]

I'm having an issue with order descending in my defines.  I'm looking to order my percentages high-low.  any glaring issues anyone can see?  I realize i'm not real proc report savvy, but i cannot figure out why my rates are not ordering correctly.  Ive attached my dataset.  any insight would be greatly appreciated.

%macro prtover;

%do I=&PREVYR. %to &CURYR.;

Title1 c=black f=calibri h=5 bold 'Month-end Campaign Summary Report';

Title2 c=black f=calibri h=3 bold "Section 3:  &i. Campaign Ranking";

Title3 c=black f=calibri h=2 bold "Campaign results are through &_PMENDDT.";

ods tagsets.excelxp  options(sheet_name="Campaign Ranking &i."

                            absolute_column_width="25,6,6,25,6,6,25,6,6,25,6"

                            );

proc report nowd data=_stack missing  out=_ret_dtc_cat_total(rename=(cat_dedup_ind=CMPGN_SEL_IND))

style(header)={font_face=calibri foreground=black font_size=2};

where CMPGN_DRP_YR =&I.;

column           cmpgn_drp_yr

                    flag

                    cmpgn_sel_ind 

                    appl_retl_ind_resp

                     ('application rate'(cmpgn_cat=cmpgn_cat4 apprate newapp))

                    dummy1

                    fund_retl_ind_resp

                    ('funding rate'(cmpgn_cat=cmpgn_cat3 fundrate newfund))

                    pif_ind_resp

                    dummy2

                    ('payoff rate'(cmpgn_cat=cmpgn_cat2 porate newpay))

                    dummy3

                    ('recapture rate' (cmpgn_cat recaprate newcap));

define cmpgn_cat4 / style=[background=white] group width=23 format=$catfmt. '';

define flag / group noprint;

define cmpgn_drp_yr / noprint group;

define cmpgn_sel_ind / noprint sum;

define appl_retl_ind_resp / noprint sum width=10;

define newapp / '' computed format=percent8.2;

define apprate / noprint order descending format=percent8.2 '';

define dummy1 / computed '' width=3 style=[background=white bordercolor=white frame=void borderwidth=1];

define cmpgn_cat3 / group width=23 format=$catfmt. '';

define fund_retl_ind_resp /noprint sum;

define newfund / '' computed format=percent8.2;

define fundrate / noprint order descending format=percent8.2 '';

define dummy2 / computed '' width=3 style=[background=white bordercolor=white frame=void borderwidth=1];

define cmpgn_cat2 / group width=23 format=$catfmt. '';

define pif_ind_resp /noprint;

define newpay / '' computed format=percent8.2;

define porate / noprint order descending format=percent8.2 '';

define dummy3 / computed '' width=3 style=[background=white bordercolor=white frame=void borderwidth=1];

define cmpgn_cat / group width=23 format=$catfmt. '';

define newcap / '' computed format=percent8.2;

define recaprate / noprint order descending format=percent8.2 '';

compute newapp;

          if apprate ^= '' then _apprate = apprate;

          newapp = _apprate;

endcomp;

compute newfund;

          if fundrate ^= '' then _fundrate = fundrate;

          newfund = _fundrate;

endcomp;

compute newcap;

          if recaprate ^= '' then _recaprate = recaprate;

          newcap = _recaprate;

endcomp;

compute newpay;

          if porate ^= '' then _porate = porate;

          newpay = _porate;

endcomp;

compute before flag /style=[foreground=black background=cxFFFF99 font_weight=bold font_face=calibri font_size=2

                                                                                          frame=box borderwidth=1 bordercolor=cx7f7f7f];

if _break_ = 'flag' and flag = 'ACQ' then do;

                    linetextc = trim(cmpgn_drp_yr||" Acquisition");

end;

if _break_ = 'flag' and flag = 'RECAP' then do;

                    linetextc = trim(cmpgn_drp_yr||" Recapture");

end;

line @1 linetextc $varying24. len;

endcomp;

compute dummy1 / char length=3;

dummy1 = '';

endcomp;

compute dummy2 / char length=3;

dummy1 = '';

endcomp;

compute dummy3 / char length=3;

dummy1 = '';

endcomp;

run;

%end;

%mend prtover;

%prtover;

ods tagsets.excelxp close;

[ballardw]

Try ORDER=DESCENDING on those percent lines.

[Cynthia_sas]

The possible values for ORDER= are ORDER=FREQ, ORDER=DATA, ORDER=FORMATTED, ORDER=INTERNAL:

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473627.htm

The keyword option, DESCENDING, is a single word option. It goes on the DEFINE statement, separately, there is no ORDER=DESCENDING option.

cynthia

[Cynthia_sas]

Hi:

  Are your COMPUTE blocks for DUMMY1, DUMMY2 and DUMMY3 correct? Also, nobody can run your code unless you also supply the user-defined formats that you are using (such as $catfmt.). And, you do not show how the values of &PREVYR, &CURYR and &_PMENDDT are provided.

  What variables do you consider to be your percentage variable? You have quite a few variables that use the PERCENT format. Remember that PROC REPORT is sorting or ORDERING from left to right so WITHIN your COLUMN statement, you have these items first:

column cmpgn_drp_yr flag cmpgn_sel_ind appl_retl_ind_resp ...more...;

and the DEFINE statements for just those 4 report items are:

define cmpgn_drp_yr / noprint group;
define flag / group noprint;
define cmpgn_sel_ind / noprint sum;
define appl_retl_ind_resp / noprint sum width=10;

  So, ordering for APPRATE, for example, would be WITHIN the ordered values for CMPGN_DRP_YR and FLAG and all the other GROUP or ORDER items that appear before APPRATE on the COLUMN statement. Is that what you are seeing?

Cynthia