Proc report: is it possible to define variable label by group?

[A_Kh]

Hello Community, 

In clinical trial multiple categorical variables are often reported together. Although they have the same number of response categories (eg. 1-5 in numeric expression), their labels differ.  In reporting we have to use labels for each categories of each variable. One way is to use just Cat1-Cat5 in the variable label, then give description of categories in the footnote for each variable. Another way is using the LINE statement and give labels to each categories by group. However, using the line statement is very time consuming since we need to work further on spacing to place the label on top of corresponding category.  If there were a workaround using DEFINE statement  to do this task, it would be way easier and more user friendly. 
Below is an example using the LINE statement. I would like to hear from you about the possible ways to make it easier. 

Thank you!

proc freq data=sashelp.cars noprint;
	where cylinders in (4, 6, 8); 
	tables origin*cylinders/out=have outpct;
run; 
data have;
	set have;
	value= cats(count, '(', put(pct_col, 4.1), '%)');
run; 
proc transpose data=have out=want prefix=Cat;
	by origin ;
	var value;
	id cylinders;
run; 
proc report data=want; 
	column origin cat4 cat6 cat8;
	define origin/order  noprint;
	define cat4/'Small' display;
	define cat6/ 'Medium' display;
	define cat8/'Large' display;
	compute before origin/style={just=l};
		line @1 text $varying100. len;
		length text $100;
		if origin ne 'Asia' then do;
			len=100;
			if origin eq 'Europe' then text= 'Smaller			Larger			Largest';
			else if origin eq 'USA' then text= 'Good 			Better			Best';
		end; 
		else do;
			len=0;
			text='';
		end; 
	endcomp;
run; 

[Cynthia_sas · Accepted solution]

Hi:

Without using a LINE statement, you can post-process your data to add a Helper variable called rowtype and then you can assign your pseudo Header values to a new row that you will insert into the data rows, as shown below:

Then, your PROC REPORT code will be simplified because now when you run your PROC REPORT on the data file with the helper variable, since you have the pseudo Headers inside the data, you can now use a COMPUTE block and CALL DEFINE to alter the style of each pseudo Header row:

  I like this method better than the LINE statement method because then I don't have to fiddle with the placement of the text strings on the LINE statement. I did need to add the LENGTH statement to my post-processing step to be sure that the new rows would have a column width big enough for my largest text string.

Cynthia

[Tom]

You need to set a length for VALUE in that code anyway since without SAS will guess to make VALUE as length $200 which will cause the PROC REPORT step to fail when you have the normal output destination open since 200 is longer than the normal setting for LINESIZE option and the PROC REPORT code did not set any other WIDTH for the columns.

811  proc report data=want;
812    column origin cat4 cat6 cat8;
813    define origin/order  noprint;
814    define cat4/'Small' display;
815    define cat6/ 'Medium' display;
816    define cat8/'Large' display;
817    compute before origin/style={just=l};
818      line @1 text $varying100. len;
819      length text $100;
820      if origin ne 'Asia' then do;
821        len=100;
822        if origin eq 'Europe' then text= 'Smaller     Larger      Largest';
823        else if origin eq 'USA' then text= 'Good      Better      Best';
824      end;
825      else do;
826        len=0;
827        text='';
828      end;
829    endcomp;
830  run;

ERROR: The width of Cat4 is not between 1 and 132. Adjust the column width or line size.
NOTE: This affects LISTING output.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 3 observations read from the data set WORK.WANT.
NOTE: PROCEDURE REPORT used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds

A simple data step can be used to add the row header observations, assuming you know how many there are in advance.

data want ;
  set want ;
  by origin;
  type=2;
  output;
  if first.origin then do;
    type=1;
    input cat4 cat6 cat8;
    output;
  end;
cards;
Small Medium Large 
Smaller Larger Largest 
Good Better Best 
;

proc report data=want; 
  column origin type cat4 cat6 cat8;
  define origin/order  noprint;
  define type / order noprint;
  define cat4/' ' display;
  define cat6/ ' ' display;
  define cat8/' ' display;
  compute type;
    if type=1 then call define(_row_,'style','style=Header');
  endcomp;
run; 

[A_Kh]

Thank you, @Cynthia_sas !

This works. The header background of each categorical variable was highlighted gray. With a slight modification of style element in call define statement I could eliminate it (I was not sure about my syntax, but it worked). 

data want1;
  set want;
  by origin;
  _origin=origin; 
  type=2;
  output;
  if first.origin then do;
    type=1;
    input cat4 cat6 cat8;
	_origin='';
    output;
  end;
cards;
Small Medium Large 
Smaller Larger Largest 
Good Better Best 
;
run; 

ods escapechar='~';
ods tagsets.excelxp file = "Table.xml" style=listing
	options(sheet_name= "T1" embedded_titles = "on" embedded_footnotes = "on" Merge_Titles_Footnotes = "on" missing_align= 'center' Row_Heights='20,16,0,32,32,0,0' Skip_Space= '0,0,0,1,1' wrap_text='yes');
	proc report data= want1 style(report)={background=white borderrightcolor=white borderleftcolor=white bordertopcolor=black borderbottomcolor=black bordertopwidth=1pt borderbottomwidth=1pt}
								 style(header)={height=24pt font_face='TREBUCHET MS' fontsize=11pt background=white borderrightcolor=white borderleftcolor=white bordertopcolor=black borderbottomcolor=black bordertopwidth=1 borderbottomwidth=1} split='~';
	  column origin type _origin  cat4 cat6 cat8;
	  define origin/order noprint;
	  define _origin/ '' width=8  style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display;
	  define type / order noprint;
	  define cat4/'' width=8  style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display;
	  define cat6/ '' width=8  style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display;
	  define cat8/'' width=8  style(column)= {just=c font_face='TREBUCHET MS' fontsize=11pt borderleftcolor=white borderleftwidth=.5pt borderrightcolor=white borderrightwidth=.5pt} display;
	  compute type;
	    if type=1 then call define(_row_,'style','style=Header {height=24pt font_face="TREBUCHET MS" fontsize=11pt background=white}');
	  endcomp;
	run; 
ods tagsets.excelxp close;