Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

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


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.


Accepted Solutions
Solution
‎05-17-2013 07:58 PM
SAS Super FREQ
Posts: 8,743

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

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;

View solution in original post


All Replies
Super User
Posts: 9,676

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

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

Contributor
Posts: 28

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

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


Contributor
Posts: 65

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

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

Solution
‎05-17-2013 07:58 PM
SAS Super FREQ
Posts: 8,743

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

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;

Super User
Posts: 9,676

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

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

Contributor
Posts: 28

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

Thank you Ksharp, this code is very helpful.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 394 views
  • 7 likes
  • 4 in conversation