BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ScottBass
Rhodochrosite | Level 12

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;


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

View solution in original post

4 REPLIES 4
Cynthia_sas
SAS Super FREQ

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
Fluorite | Level 6

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;


ScottBass
Rhodochrosite | Level 12

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;


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

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

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 1200 views
  • 3 likes
  • 4 in conversation