BookmarkSubscribeRSS Feed
ja_lew
Fluorite | Level 6

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;

3 REPLIES 3
ballardw
Super User

Try ORDER=DESCENDING on those percent lines.

Cynthia_sas
SAS Super FREQ

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
SAS Super FREQ

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 704 views
  • 0 likes
  • 3 in conversation