PROC REPORT - can I override format for a numeric column for a single row?

[ScottBass]

Hi,

I need to create a "Top 20" report, with sub-totals for the Top 20, grand totals for all records, and percentage contribution of Top 20 vs. all records.

Say the numeric variables are formatted as comma.  Is it possible, for the final percentage row, to override that format with percent?

I know one workaround:  convert all the numeric columns to character, the use the put function to save the formatted character equivalent.

Here is some sample code (pretend it is a "Top 5" report).  As always, my actual problem is more complex than this.  My preferred approach would be to override the column format for the percentage row only.

Thanks,

Scott

data one;

  length label $50 n1-n6 8;

  do n1=1 to 20;

    label=cat("Line",n1);

    n2=n1*2;

    n3=n1*3;

    n4=n1*4;

    n5=n1*5;

    n6=n1*6;

    output;

  end;

run;

data two;

  set one end=eof;

  array vars n1--n6;

  array sums{2,6} _temporary_;

  if _n_ le 5 then do;

    do i=1 to dim2(sums);

       sums{1,i}+vars{i};

    end;

    output;

  end;

  do i=1 to dim2(sums);

     sums{2,i}+vars{i};

  end;

  if eof then do;

     label="Sub-total for Top 5";

     do i=1 to dim2(sums);

        vars{i]=sums{1,i};

     end;

     output;

     label="Total for All Values";

     do i=1 to dim2(sums);

        vars{i]=sums{2,i};

     end;

     output;

     label="Percent of Top 5";

     do i=1 to dim2(sums);

        vars{i}=sums{1,i}/sums{2,i};

     end;

     output;

  end;

  drop i;

run;

data three;

  set two;

  length c1-c6 $8;

  array num{*} n1-n6;

  array chr{*} c1-c6;

  do i=1 to dim(num);

     chr{i}=ifc(index(label,"Percent"),put(num{i},percent.),put(num{i},comma.));

  end;

  drop i;

run;

options nocenter ls=max;

* does not work ;

proc report data=two nowd;

  format n1-n6 comma6.;

quit;

* works but is a pain ;

proc report data=three nowd;

  columns label c1-c6;

  define c1     / display right;

  define c2     / display right;

  define c3     / display right;

  define c4     / display right;

  define c5     / display right;

  define c6     / display right;

quit;

[Cynthia_sas · Accepted solution]

Hi:

Not at a computer with SAS right now, but I know that there have been previous forum postings about using CALL DEFINE to set either style or a format. A Google search or a forum search should help you narrow down to some examples.

  It will be something like this:

rbreak after / summarize;

compute after;

    call define ('myvar.sum','format','percent9.2');

/* or */

    call define('myvar','format','percent9.2');

endcomp;

The syntax that you use will depend on whether you have defined MYVAR as a COMPUTED item or as an ANALYSIS item (with the SUM statistic, for example).

cynthia

[Alpay]

Took Cynthia's idea and tried the followimg

%macro pctfmt(v);

  compute &v;

    if label =: 'Percent' then do;

    call define("&v",'format','percent9.2');

    end;

  endcomp;

%mend;

proc report data=two nowd;

  columns label n1-n6;

  define n1-n6     / display right;

  %pctfmt(n1);

  %pctfmt(n2);

  %pctfmt(n3);

  %pctfmt(n4);

  %pctfmt(n5);

  %pctfmt(n6);

run;

instead of

* works but is a pain ;

proc report data=three nowd;

  columns label c1-c6;

  define c1     / display right;

  define c2     / display right;

  define c3     / display right;

  define c4     / display right;

  define c5     / display right;

  define c6     / display right;

quit;

[RD2]

Data _null_' s solution:

http://listserv.uga.edu/cgi-bin/wa?A2=ind1107b&L=sas-l&T=0&P=4394

[ScottBass]

Hi All,

Thanks for your replies, esp. Cynthia.  Much appreciated.

I'm using a computed, noprint dummy variable to contain my compute block.  If there's a better approach, let me know.

My final version (sorry for the length, relevant bits in yellow):

%macro print_topx_report;

  %let data=spdework.topx_source_data;

  %let nodata=%varexist(&data,message);  <<< if there was no source data, I create a msg dataset upstream and print it instead.  it will contain the variable "message" ;

  proc report data=&data nowd spanrows split="*";

    columns

    %if (&nodata) %then %do;

message

    %end;

    %else %do;

      label

        ("Period 1"

period1_col1

period1_col2

period1_col3

        )

        ("Period 2"

period2_col1

period2_col2

period2_col3

        )

        ("% Change"

change_col1

change_col2

change_col3

        )

dummy  <<< dummy variable used to contain "code" ;

    %end;

    ;

    %if (&nodata) %then %do;

    define message                    / display     center              style(column)=[cellwidth=195mm] "0A"x;  <<< prints a blank header (no varname or label)

    %end;

    %else %do;

    define label                      / display style(column)=[cellwidth=101mm];

    define period1_col1               / analysis    format=comma16. style(column)=[cellwidth=11mm];

    define period1_col2               / analysis    format=dollar16. style(column)=[cellwidth=20mm];

    define period1_col3               / analysis    format=dollar16.    style(column)=[cellwidth=24mm];

    define period2_col1               / analysis    format=comma16. style(column)=[cellwidth=11mm];

    define period2_col2               / analysis    format=dollar16.    style(column)=[cellwidth=20mm foreground=green];

    define period2_col3               / analysis    format=dollar16. style(column)=[cellwidth=24mm];

    define change_col1                / computed    format=blankpct. style(column)=[cellwidth=13mm background=highlight.] "Label1";

    define change_col2                / computed    format=blankpct. style(column)=[cellwidth=20mm background=highlight.] "Label2";

    define change_col3                / computed    format=blankpct. style(column)=[cellwidth=24mm background=highlight.] "Label3";

    define dummy                      / computed    noprint;  <<< the dummy variable is not printed

    compute change_col1;  <<< compute percent change between period 1 and period 2

      if (period2_col1.sum ne 0) then

change_col1                   = (period2_col1.sum - period1_col1.sum) / period2_col1.sum;

      else

change_col1                   = 0;

endcomp;

    compute change_col2;

      if (period2_col2.sum ne 0) then

change_col2                   = (period2_col2.sum - period1_col2.sum) / period2_col2.sum;

      else

change_col2                   = 0;

endcomp;

    compute change_col3;

      if (period2_col3.sum ne 0) then

change_col3                   = (period2_col3.sum - period1_col3.sum) / period2_col3.sum;

      else

change_col3                   = 0;

endcomp;

    compute dummy;

      if prxmatch("/Total|Percent/io",label) then do;

        call define(_row_,"style","style=DataEmphasis");  <<< I create the Totals and Percent rows in an upstream data step.  I want it to look like a PROC REPORT summary line.

      end;

      if prxmatch("/Percent/io",label) then do;

        call define("period1_col1.sum","format","blankpct.");  <<< see below for blankpct format.

        call define("period1_col2.sum","format","blankpct.");

        call define("period1_col3.sum","format","blankpct.");

        call define("period2_col1.sum","format","blankpct.");

        call define("period2_col2.sum","format","blankpct.");

        call define("period2_col3.sum","format","blankpct.");

      end;

    endcomp;

    %end;

  quit;

%mend;

proc format;

  * format to print blanks for special missing numeric values ;

  value blankpct

    .Z              = " "

other            = [percent8.1]

  ;

quit;