The SAS Output Delivery System and reporting techniques

Change numeric format for one row in a table

Reply
Frequent Contributor
Posts: 102

Change numeric format for one row in a table

I have a PROC REPORT statement that produces an n by m table with percentages, means and medians.  The first data row is the count or 'N.'  I want to retain one decimal for all cells in the table except for the first row where I am only interested in the integer.  Is there a way to apply format=comma8.0 to the first row of the table and format=comma9.1 to the rest.

Much obliged,

Haris

SAS Super FREQ
Posts: 8,743

Re: Change numeric format for one row in a table

Hi:

  Using the CALL DEFINE statement, you can change the format for a particular row. Since you did not post data, I made some up using SASHELP.CLASS. Of course, it is silly to format MIN and MAX with dollar signs and N with decimal places, but you get the general idea when you run this code. In my program, I used the value of the _STAT_ cell to determine the format. If your need is more arbitrary, then you would have to investigate other ways of identifying only the first row or first cell in the table.or the first row for a group. The only thing to remember is that PROC REPORT does not have an OBS column like PROC PRINT, A COMPUTE block can help you there, as well, but why not try this first and see whether it leads you anyplace.
  

cynthia

proc means data=sashelp.class nway;

var height;

class sex;

output out=work.mn;

run;

      

ods listing close;

ods html file='c:\temp\diff_formats.html';

proc report data=work.mn nowd;

  column sex _stat_ height;

  define sex / order;

  define _stat_ / display 'Statistic';

  define height / 'Value';

  compute height;

    if _stat_ = 'N' then

       call define(_col_,'format','8.3');

    else if _stat_ = 'MIN' then

       call define(_col_,'format','dollar9.0');

    else if _stat_ = 'MAX' then

       call define(_col_,'format','dollar12.2');

    else if _stat_ = 'MEAN' then

       call define(_col_,'format','9.1');

    else if _stat_ = 'STD' then

       call define(_col_,'format','8.0');

  endcomp;

run;

ods html close;

Frequent Contributor
Posts: 102

Re: Change numeric format for one row in a table

Thank you Cynthia,

This code works for what I need; however, I have ten columns in my table.  The equivalent of adding weight as well as seven additional numeric columns to the example you created.  I can write 9 COMPUTE statements but is there a way to apply the same formats to the entire row rather than just one cell?  I tried replacing _COL_ with _ROW_ but that does not seem to work.  Listing Weight next to Height on the COMPUTE line also does not work.

Appreciate your help!

SAS Super FREQ
Posts: 8,743

Re: Change numeric format for one row in a table

Hi:

  You are correct, using _ROW_ probably does not work, as you desire, because the TAGATTR might not be appropriate for every report item on the report row. Again since you did not post data, it is hard to comment or visualize what you mean when you say adding "seven additional columns". You would not need 9 COMPUTE blocks. As I show in the code below -- I'm changing 3 variables in 1 COMPUTE block. So whether you have 1 variable or 3 variables, 7 variables or 20 variables, you can change them in 1 COMPUTE block, you just have to pick the correct item to change.

  So, you have a couple of choices, I show how to do it 2 ways in one COMPUTE block. First, you can venture into ARRAYS; then, if you're comfortable with that, if you know SAS Macro Language, you can "macro-ize" your code even more to generate the entire COMPUTE block. The key to either of the code examples below is knowing that a COMPUTE block for the "farthest"  item (WEIGHT is the "right-most" item in the COLUMN statement) can be used to issue a CALL DEFINE for any of the items to the left. So, since AGE and HEIGHT are to the left of WEIGHT, they can be changed in the COMPUTE block for WEIGHT. However, HEIGHT and WEIGHT could NOT be changed in a COMPUTE block for AGE -- because PROC REPORT works from left to right in populating the report row. And, at the point in time when AGE is being written, HEIGHT and WEIGHT are not on the report row yet. But by the time WEIGHT is put on the report row, AGE and HEIGHT have already been placed on the row, so all 3 items can be changed within the COMPUTE block for WEIGHT.

  So, it's possible to streamline the REPORT code. I just used 5 obs from SASHELP.CLASS in the code below. Since you didn't show your data or make some fake data I just changed each row based on the value of the NAME variable in SASHELP.CLASS (which is similar to changing the value based on the _STAT_ item in the previously posted programs).

cynthia


ods html file='c:\temp\something_else.html';
 
proc report data=sashelp.class nowd;
  title 'A) Different Format for Multiple Items on a Row';
  where name in ('Alice', 'Alfred', 'Barbara', 'Janet', 'William');
  column name sex age height weight;
  define name / order;
  define sex / order;
  define age / display;
  define height / display;
  define weight / display;
  compute weight;
        if name = 'Alfred' then do;
           call define('age','format','dollar9.0');
           call define('height','format','dollar9.0');
           call define(_col_,'format','dollar9.0');
        end;
        else if name = 'Alice' then do;
           call define('age','format','8.3');
           call define('height','format','8.3');
           call define(_col_,'format','8.3');
        end;
        else if name = 'Barbara' then do;
           call define('age','format','dollar12.2');
           call define('height','format','dollar12.2');
           call define(_col_,'format','dollar12.2');
        end;
        else if name = 'Janet' then do;
           call define('age','format','9.1');
           call define('height','format','9.1');
           call define(_col_,'format','9.1');
        end;
        else if name = 'William' then do;
           call define('age','format','8.0');
           call define('height','format','8.0');
           call define(_col_,'format','8.0');
        end;
  endcomp;
run;

ods html close;
   
ods html file='c:\temp\use_array.html';

proc report data=sashelp.class nowd;
  where name in ('Alice', 'Alfred', 'Barbara', 'Janet', 'William');
  title 'B) Different Format using Array Processing';
  column name sex age height weight;
  define name / order;
  define sex / order;
  define age / display;
  define height / display;
  define weight / display;
  compute weight;
      length fmtval $15;
      array var(3) $ ("age" "height" "weight" );
      do i=1 to dim(var);
         if name = 'Alfred' then fmtval = 'dollar9.0';
         else if name = 'Alice' then fmtval = '8.3';
         else if name = 'Barbara' then fmtval = 'dollar12.2';
         else if name = 'Janet' then fmtval = '9.1';
         else if name = 'William' then fmtval ='8.0';
         call define(var(i),'format',trim(fmtval));
      end;
  endcomp;
run;

ods html close;

Ask a Question
Discussion stats
  • 3 replies
  • 379 views
  • 0 likes
  • 2 in conversation