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


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.

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

6 REPLIES 6
Ksharp
Super User

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

OPHD1
Fluorite | Level 6

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


KarlK
Fluorite | Level 6

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

Cynthia_sas
SAS Super FREQ

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;

Ksharp
Super User

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

OPHD1
Fluorite | Level 6

Thank you Ksharp, this code is very helpful.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

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

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 2068 views
  • 7 likes
  • 4 in conversation