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.
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;
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
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
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
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;
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
Thank you Ksharp, this code is very helpful.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.