Proc report not ordering values?

ja_lew — Wed, 02 Nov 2011 19:12:30 GMT

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;

Proc report not ordering values?

ballardw — Wed, 02 Nov 2011 21:42:00 GMT

Try ORDER=DESCENDING on those percent lines.

Re: Proc report not ordering values?

Cynthia_sas — Wed, 02 Nov 2011 23:18:33 GMT

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

Re: Proc report not ordering values?

Cynthia_sas — Wed, 02 Nov 2011 23:22:34 GMT

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

topic Re: Proc report not ordering values? in SAS Procedures

Proc report not ordering values?

Proc report not ordering values?

Re: Proc report not ordering values?

Re: Proc report not ordering values?