proc report repeat value question

[ja_lew]

proc report nowd data=  r2011 missing split='|';

          column

          cmpgn_cat

          apprate;

define cmpgn_cat / '' style(column)={font_face=calibri background=white foreground=black font_size=1} format=$catfmt.;

define apprate / style(column)={font_face=calibri background=white foreground=black font_size=1} order descending format=percent8.2 '';

          compute before / style={font_weight=bold font_face=calibri font_size=1 foreground=white background=cx808000 frame=void borderwidth=0 bordercolor=cx000000};

                    linetext = 'Recapture/Acquisition';

          line @1 linetext $30.;

          endcomp;

run;

how do i get proc report to display all values?  as in the picture cat3 and cat4 are both 0.18% but only displays the first instance of that value.  is there a way to have it show all?

[art297]

Can you also post your data file?

[ja_lew]

i can create a dummy file, unfortunately the data is restricted by confidentiality agreements.  gimme a few minutes.

[ja_lew]

proc report nowd data=  r2011_copy missing split='|';

where flag = 'a';

          column

          cmpgn_cat

          fundrate;

define cmpgn_cat / '' style(column)={font_face=calibri background=white foreground=black font_size=1} format=$catfmt.;

define fundrate / style(column)={font_face=calibri background=white foreground=black font_size=1} order descending format=percent8.2 '';

          compute before / style={font_weight=bold font_face=calibri font_size=1

                                                                      foreground=white background=cx800080

                                                                      frame=void borderwidth=0 bordercolor=cx000000};

                    linetext = 'Acquisition fund';

          line @1 linetext $30.;

          endcomp;

run;

you'll see with rounding cat9 and cat10 both are 0.18% and only display for 1 category.

[art297]

I am definitely NOT an expert with proc report, but I think that is the proc's action when an order statement is included.  Would you get want you want if you left the order out and, instead, presorted your data?  e.g.:

proc sort data=r2011;

  by descending apprate;

run;

proc report nowd data=  r2011 missing split='|';

          column

          cmpgn_cat

          apprate;

define cmpgn_cat / '' style(column)={font_face=calibri background=white foreground=black font_size=1} format=$catfmt.;

define apprate / style(column)={font_face=calibri background=white foreground=black font_size=1} /*order descending*/ format=percent8.2 '';

          compute before / style={font_weight=bold font_face=calibri font_size=1 foreground=white background=cx808000 frame=void borderwidth=0 bordercolor=cx000000};

                    linetext = 'Recapture/Acquisition';

          line @1 linetext $30.;

          endcomp;

run;

[ja_lew]

i very well could and yes that resolves the issue however it's not as 'automated' as I would like.  the entire report has around 70-80 reports based on different metrics and it's rare that the rounding issue shows up.  i dont know how feasible it would be to recode with proc sorts everytime that instance comes up.  i guess im kinda hoping that there is a way to use order descending and have it display all values.. maybe wishful thinking..

[art297 · Accepted solution]

The following post describes a couple of ways to do it: http://listserv.uga.edu/cgi-bin/wa?A2=ind0301d&L=sas-l&H=1&P=39488

[ja_lew]

perfect!!! not very elegant but it works thanks Art!  with a lil bit of recoding i'll have this done.  nice.

proc report nowd data=  r2011_copy missing split='|';

where flag = 'a';

          column

          cmpgn_cat

          fundrate

          newfund;

define cmpgn_cat / '' style(column)={font_face=calibri background=white foreground=black font_size=1} format=$catfmt.;

define newfund /computed format=percent8.2;

define fundrate / noprint style(column)={font_face=calibri background=white foreground=black font_size=1} order descending format=percent8.2 '';

compute newfund;

          if fundrate ^= '' then _fundrate = fundrate;

          newfund = _fundrate;

endcomp;

          compute before / style={font_weight=bold font_face=calibri font_size=1

                                                                      foreground=white background=cx800080

                                                                      frame=void borderwidth=0 bordercolor=cx000000};

                    linetext = 'Acquisition fund';

          line @1 linetext $30.;

          endcomp;

run;