how to dynamically get a "row header" to break without using ODS ESCAPECHAR= in PROC REPORT

[Varrelle]

Hi SAS community,

I have a question RE: line breaks for specific cell values generated dynamically during the creation of the PROC report input data set.

I will define "row header" as the cell located at the intersection of a given row and the first visible column.

I want certain "row headers" to break to a new line. In the program below, the "row header" cell is generated dynamically and so that is why I believe I am unable to insert an ODS ESCAPECHAR into the cell value to get it to break to the next line. Is there another way to do this?

I've created a simple program using the sashelp.cars DATASET to give more context to my question/post. The program is embedded below and the .RTF output is atached.

My goal is to have the cell at the intersection of "col1" and roword=1 to break after the colon (":").

Currently, the line reads as follows:

"This is: the row header. 60 SUVs, 3 Hybrids, for a total of 63"

I want it to break as if I were using the ODS ESCAPECHAR="~" below after "This is:":

"This is: ~n the row header. 60 SUVs, 3 Hybrids, for a total of 63"

But, as I said, I cannot rely on being able to insert such a character because the cell value is generated dynamically.

Hope someone can help me.

Thanks and best wishes.

proc contents data=sashelp.cars varnum; run;

data hybrid_suv (keep=hp mpgc mpgh type origin);
set sashelp.cars
(rename=(horsepower=hp mpg_city=mpgc mpg_highway=mpgh));
where type="Hybrid"|type="SUV";
run;

ods output summary=summary_hybrid_suv;
proc means data=hybrid_suv mean std;
class type;
ways 0 1;
var 
hp ;
run;

data _null_;
set summary_hybrid_suv;
if type=" " then call symputx ("n_all",put(nobs,3.));
if type="SUV" then call symputx ("n_suv",put(nobs,3.));
if type="Hybrid" then call symputx ("n_hyb",put(nobs,3.));
run;

data hp_summary2 (keep=case hp hp_m hp_s type);
length case 8 hp $25 hp_m hp_s $6;
set summary_hybrid_suv;
if type=" " then case=2; else
if type="SUV" then case=1; else
if type="Hybrid" then case=0;
hp="Horsepower, mean (SD)";
hp_m=put(hp_mean,5.1);
hp_s=put(hp_stddev,5.1);
run;

data hp_summary3;
set hp_summary2 end=last;
retain mean1 std1 mean0 std0 mean2 std2;
array means (0:2) $5 mean0 mean1 mean2;
array stds (0:2) $5 std0 std1 std2;
means (case) = hp_m;
stds (case) = hp_s;
m_suv=put(mean1,5.1)||" ("||strip(put(std1,5.1))||")";
m_hyb=put(mean0,5.1)||" ("||strip(put(std0,5.1))||")";
m_all=put(mean2,5.1)||" ("||strip(put(std2,5.1))||")";
if last;
keep hp m_suv m_hyb m_all;
run;

data hp_summary4;
length
roword
grpord
type
8
col1 $200
col2 col3 col4 $20;
;

label roword="This is: the row header";

set hp_summary3; 
retain grpord 1 brkvar "y";
if _n_=1 then do;
type=1;
col1=vlabel(roword)||". "||"&n_suv SUVs, &n_hyb Hybrids, for a total of &n_all";
col2=" ";
col3=" ";
col4=" ";
roword+1;
output;
end;
type=0;
roword+1;
col1=hp;
col2=m_suv;
col3=m_hyb;
col4=m_all;
output;

keep roword grpord type col1 col2 col3 col4;

run;


/*%let path=C:\Users\****\Desktop;*/
ods rtf path="&path";
ods rtf file="row_header_test1.rtf" style=journal;
proc report data=hp_summary4;
column col1 col2 col3 col4;
define col1/display "Horsepower";
define col2/display "SUV";
define col3/display "Hybrid";
define col4/display "SUV and Hybrid";
run;
ods rtf close;

[Cynthia_sas]

Hi:

  I'm sorry, I don't understand where you want the break to be. I've highlighted several possible places in green:

You could want only the total of 63 on a line by itself or you could want the line break in other places. Your RTF file did not indicate how you wanted the output to be changed. You're creating that line in your code, why can't you insert a newline and ESCAPECHAR?

Cynthia

[Cynthia_sas]

Hi:

  In thinking about it, if you just used multiple LINE statements in a COMPUTE before, then every LINE statement starts a separate line in the report output, like this:

  I was also able to create this report directly from HPSUMMARY with just a few changes in the program, and a user-defined format. Mostly, I used CASE as an ACROSS variable and calculated a new character column called BOTH that was the value used under SUV, Hybrid and the combo.  So there was no need for creating HPSUMMARY3 or HPSUMMARY4.

  Here's a paper I wrote about COMPUTE blocks and break processing: https://support.sas.com/resources/papers/proceedings17/SAS0431-2017.pdf . COMPUTE blocks are pretty cool.

  Here's the program. Everything above HP_SUMMARY2 was the same as your program, but I changed the HPSUMMARY2 DATA step.

data hp_summary2 (keep=case hp hp_m hp_s type);
length case 8 hp $25 hp_m hp_s $6;
set summary_hybrid_suv;
  ** change value of case to use format and ACROSS;
  if type=" " then case=3; 
  else if type="SUV" then case=1;  
  else if type="Hybrid" then case=2;
  hp="Horsepower, mean (SD)";
  hp_m=put(hp_mean,5.1);
  hp_s=put(hp_stddev,5.1);
run;

proc format;
   value cfmt 1='SUV'
              2='Hybrid'
              3='SUV &/Hybrid';
run;
  
proc sort data=hp_summary2;
  by case hp;
run;
    
ods rtf file="c:\temp\alt_header_test1.rtf" style=journal;

proc report data=hp_summary2 split='/'
  style(header)={vjust=b}
  style(lines)={just=left fontweight=bold background=white foreground=black};
  column ('Horsepower' hp) case,(hp_m hp_s both) n;
  define hp / group ' ';
  define case / across ' ' f=cfmt. order=data;
  define hp_m / display ' ' noprint;
  define hp_s / display ' ' noprint;
  define both/computed ' ';
  define n / noprint;
  compute both / character length=15;
   _c4_ = catt(_c2_,' (',strip(_c3_),')');
   _c7_ = catt(_c5_,' (',strip(_c6_),')');
   _c10_ = catt(_c8_,' (',strip(_c9_),')');
  endcomp;
  compute before hp;
    line "This is: Alternate method";
	line "&n_suv SUVs,";
	line "&n_hyb Hybrids,";
	line "for a total of &n_all..";
  endcomp;
 run;
 ods rtf close;

Cynthia

[Varrelle]

Wow -- what a neat program!

Those compute blocks are cool, indeed! I'm looking forward to reviewing your paper.

Before I do though, may I ask if it would it be possible to target a cell value in say the 5th row down, using a compute block?  For example, if I had a PROC REPORT input data set with 10 rows and 7 columns, with the columns specified as I had in the code example I provided in my original post (grpord, roword, type, col1, col2 col3, col4), could I use the roword columns to tell the compute block to manipulate the cell value for the 5th row cell value in column 1?

something like:

compute before roword=5...;

    ... SAS programming statements...;

endcomp; 

Thanks for your help.

[Cynthia_sas]

Hi:

 No and yes, but, you'll have to read the paper. There are only certain places you can use a COMPUTE block and the syntax you show would NOT be valid. There is a way to do it, you can test the value of roword (which means it has to be in the COLUMN) statement. I have an example in the code that you can download for this paper: https://support.sas.com/resources/papers/proceedings/pdfs/sgf2008/173-2008.pdf and you can download the programs from: http://support.sas.com/rnd/papers/#RegUsers_2008 --scroll down to the papers for 2008 and look for the paper title. The zip download is there too.

Cynthia

[Varrelle]

Ah ok - I understand. Thanks for pointing me to all of these great resources.
Best wishes