topic Proc Tabulate - Multiple Formats In One Column and Header Rows Within Table in SAS Procedures

Proc Tabulate - Multiple Formats In One Column and Header Rows Within Table

OPHD1 — Thu, 16 May 2013 18:51:14 GMT

I have two questions related to proc tabulate.

1) How can I use multiple formats within one column, e.g. 8., 8.1, percent8.1

2) How can I insert a header row within the the data table? This row will have no data, just the header and I will need to indicate where it is placed.

For example,

data sample;

length measure $ 50;

input measure $ group $ result;

datalines;

Measure1 Group1 123.456

Measure1 Group2 234.567

Measure1 Group3 345.678

Measure2 Group1 34.56

Measure2 Group2 45.67

Measure2 Group3 56.78

Measure3 Group1 .4999

Measure3 Group2 .5999

Measure3 Group3 .6999

Measure4 Group1 .4456

Measure4 Group2 .5567

Measure4 Group3 .6678

;

run;

proc format;

value $sample 'Measure1' = '0in'

'Measure2' = '0in'

'Measure3' = '0.25in'

'Measure4' = '0.25in';

quit;

proc tabulate data=sample order=data;

class measure group;

classlev measure/ style = {indent=$sample. cellwidth=1.75in};

var result;

table measure = ' ',

group = ' ' *(result = ' ')*max = ' ';

run;

I'd like to:

1) Insert a row called 'MeasureType' before Measure3. (not to be indented, which is easy to do with the proc format). There will be no values for the Group1, Group2, Group3 columns.

2) a. Format Measure1 as 8.

2) b. Format Measure2 as 8.1

2) c. Format Measure3 and Measure4 as percent8.1

Thanks very much.

Re: Proc Tabulate - Multiple Formats In One Column and Header Rows Within Table

Ksharp — Fri, 17 May 2013 03:06:33 GMT

Not Sure if it is what you want .

data sample;
     length measure $ 50;
     input measure $ group $ result;
     datalines;
     Measure1 Group1 123.456
     Measure1 Group2 234.567
     Measure1 Group3 345.678
     Measure2 Group1 34.56
     Measure2 Group2 45.67
     Measure2 Group3 56.78
     Measure3 Group1 .4999
     Measure3 Group2 .5999
     Measure3 Group3 .6999
     Measure4 Group1 .4456
     Measure4 Group2 .5567
     Measure4 Group3 .6678
;
run;

proc sql;
create table x as
 select 'MeasureType' as  measure,group
  from (select distinct group from sample )
 outer union corresponding
 select * from sample ;
quit;

proc format;
     value $sample          'Measure1' = '0in'
                                   'Measure2' = '0in'
                                   'Measure3' = '0.25in'
                                   'Measure4' = '0.25in';
      value fmt
            1='Measure1'
            2='Measure2'
               3='MeasureType'
            4='Measure3'
            5='Measure4';
     value re
           100-high=[8.]
            1-100=[8.1]
            low-1=[percentn8.]
quit;
data temp;
 set x;
 if measure='Measure1' then m=1;
  else if measure='Measure2' then m=2;
   else if measure='MeasureType' then m=3;
    else if measure='Measure3' then m=4;
      else if measure='Measure4' then m=5;
format m fmt. result re.;
run;
options missing=' ';
proc tabulate data=temp order=internal;
     class m group;
     classlev m/ style = {indent=$sample. asis=on cellwidth=1.75in};
     var result;
     table m = ' ',
          group = ' ' *(result = ' ')*max = ' '*format=re10.;
run;

Ksharp

Re: Proc Tabulate - Multiple Formats In One Column and Header Rows Within Table

OPHD1 — Fri, 17 May 2013 19:39:15 GMT

Thank you, Ksharp. This was very helpful.

I have two remaining questions off this --

1) the $sample. format did not indent the rows, the following error message appeared: "WARNING: Wrong type of format for data type: $sample." I am wondering what this message appeared

2) Is it possible to set the re format based on Measure name instead of Measure values? The values bounce around in the real data, and defining the formats off the values will not be accurate in assigning the formats. I tried to use:

value re

'Measure1' = [8.]

'Measure2' = [8.1]

'Measure3' = [percent8.1]

'Measure4' = [percent8.1];

but received an error message that I couldn't assign a numeric format to a quoted string

Re: Proc Tabulate - Multiple Formats In One Column and Header Rows Within Table

KarlK — Fri, 17 May 2013 20:55:08 GMT

PMFJI, but I was wondering if Ksharp meant the "sample" format to be numeric, like so:

value sample 1,2,3 = '0in'

4,5 = '0.25in';

Then, of course, the style attribute is

classlev m/ style = {indent=sample. asis=on cellwidth=1.75in};

i.e., "sample" not "$sample". Seems to work better. Just a guess on my part.

Karl

Re: Proc Tabulate - Multiple Formats In One Column and Header Rows Within Table

Cynthia_sas — Fri, 17 May 2013 23:58:07 GMT

Hi, Sorry for arriving late at the party. Is this example at all related to your previous posting about the Measure issue and indenting?

https://communities.sas.com/message/165826

If so, it would be nice to know that. There was code posted in that example that might have been relevant here. For example, it is easy to change formats the way you want, in PROC REPORT. As just an example...see below. Only 1 format needed for the indent.

cynthia

data orig_dat;
     infile datalines delimiter = ',';
     length Measure $ 12;
     input Measure $ Group $ Rate LCL UCL @@;
     datalines;
     Measure1,Group1,2345,1234,3456
     Measure1,Group2,3345,2234,4456
     Measure1,Group3,4345,3234,5456
     Measure2,Group1,234,123,456
     Measure2,Group2,345,234,456
     Measure2,Group3,456,345,678
     ;
run;

proc format;
value $measf 'Measure1' = '0in'
               'Measure2' = '.25in';
run;

ods listing close;
ods html file='c:\temp\diff_fmt.html' style=sasweb;
ods rtf file='c:\temp\diff_fmt.rtf';
ods pdf file='c:\temp\diff_fmt.pdf';

proc report data=orig_dat nowd;
    title '1) Using PROC REPORT with CALL DEFINE';
    title2 'Note different formats for RATE column based on MEASURE value';
    column measure group,(rate lcl ucl);
    define measure / group
           style(column)={cellwidth=2in};
    define group /across ' ';
    define rate / max;
    define lcl / max;
    define ucl / max;
    compute rate;
      if measure = 'Measure1' then do;
         call define('_c2_','format','8.3');
         call define('_c5_','format','8.3');
         call define('_c8_','format','8.3');
      end;
      else if measure = 'Measure2' then do;
         call define('_c2_','format','8.1');
         call define('_c5_','format','8.1');
         call define('_c8_','format','8.1');
      end;
    endcomp;
    compute measure;
      if measure = 'Measure1' then do;
         call define(_col_,'style','style=RowHeader');
      end;
      else if measure = 'Measure2' then do;
         call define(_col_,'style','style=RowHeader{indent=.25in}');
      end;
    endcomp;
   run;
ods _all_ close;

Re: Proc Tabulate - Multiple Formats In One Column and Header Rows Within Table

Ksharp — Sat, 18 May 2013 02:32:37 GMT

OK.

data sample;
     length measure $ 50;
     input measure $ group $ result;
     datalines;
     Measure1 Group1 123.456
     Measure1 Group2 234.567
     Measure1 Group3 345.678
     Measure2 Group1 34.56
     Measure2 Group2 45.67
     Measure2 Group3 56.78
     Measure3 Group1 .4999
     Measure3 Group2 .5999
     Measure3 Group3 .6999
     Measure4 Group1 .4456
     Measure4 Group2 .5567
     Measure4 Group3 .6678
;
run;

proc sql;
create table x as
 select 'MeasureType' as  measure,group
  from (select distinct group from sample )
 outer union corresponding
 select * from sample ;
quit;

proc format;

      value fmt
            1='Measure1'
            2='Measure2'
               3='MeasureType'
            4='~_~_~_~_Measure3'
            5='~_~_~_~_Measure4';
     value re
           100-high=[8.]
            1-100=[8.1]
            low-1=[percentn8.]
quit;
data temp;
 set x;
 if measure='Measure1' then m=1;
  else if measure='Measure2' then m=2;
   else if measure='MeasureType' then m=3;
    else if measure='Measure3' then m=4;
      else if measure='Measure4' then m=5;
format m fmt. result re.;
run;
options missing=' ';

ods listing close;
ods html file='c:\temp\diff_fmt.html' style=sasweb;
ods escapechar='~';
proc tabulate data=temp order=internal;
     class m group;
     classlev m/ style = { asis=on cellwidth=1.75in};
     var result;
     table m = ' ',
          group = ' ' *(result = ' ')*max = ' '*format=re10.;
run;
ods _all_ close;

Ksharp

Re: Proc Tabulate - Multiple Formats In One Column and Header Rows Within Table

OPHD1 — Mon, 20 May 2013 11:48:20 GMT

Thank you Ksharp, this code is very helpful.