Insert Blank Rows into Proc Tabulate

[rcleven2]

Attached in the Zip file are two sets of Code, one that has formats used in the dataset, and another that has the exact proc Tabulate code I use. There is also an image of my output with a drawing of where I need blank rows inserted and a dataset that file.

Your Mission should you choose to accept it - To insert blank lines into the Proc Tabulate output between my class variables as marked on the image.

[ballardw]

It may depend on the exact meaning you have for "blank line" and the actual tabulate code needed. If you just want a little bit of white space you might be able to create a format that assigns more height to a top row of a group creating the space. Or perhaps the option NOCELLMerge might help.

One way for some types out output: Key is creating a format with 1) a value that does not actually appear in the data, 2) that would be the  last (by default sort order) value if it were present, 3) assign a display value of blank - the only value that does so and 4) use the PRELOADFMT option with the variable and assign the appropriate format.

Proc format library=work;
value $bsex   (notsorted)
'F' = 'Female'
'M' = 'Male'
'X' = ' '
;
value bage (notsorted)
11 = '11'
12 = '12'
13 = '13'
14 = '14'
15 = '15'
16 = '16'

999 =' '
;


proc tabulate data=sashelp.class;
   class sex /preloadfmt;
   format sex $bsex.;
   class age/preloadfmt;
   format age bage.;
   var height;
   table sex age,
         height*(mean max min)
         /printmiss misstext=' '
   ;
run;

The Preloadfmt option has some moderately serious restrictions on how it works. You can't reference another format as the range display for instance  ( 1 - 23 = [best3.] for example) , requires either the PRINTMISS option or ORDER=DATA.

HOWEVER some statistics, like N will attempt to display 0. So you would also need a custom format to use with N to display 0 as a blank and explicitly use that format with the N statistic such as N*f=nozero. in the body of a table.

I have forced this by having values added to the data with a value that would not appear in actual result values, such as -9999 for age, and then used a format to display the value as " " (blank value), which included some specific values of a single row header variable, and about 8 custom formats to control the color of text (foreground=background), row heights, and a number of other appearance options such as conditional border and background colors.

But that may take a lot of data manipulation and in fact the step I used this for first created an output data set from proc tabulate, went through a data step to manipulate row headers and insert the key values and then used another proc tabulate to display the result. The only reason this was at all worth the effort was because to get similar multiple nested column headers and spanning that I wanted was going to be even worse for Proc Report.

ZIP files have so many potentials for problems that many won't download them from unknown sources, or like my organization have rules against such. Post code by copy and paste into a code box opened on the forum with the {I} or "running man" icon. Attach images using the Photos Icon.

You may want to consider summarizing the data and then proc report or possibly the data step Report Writing Interface which lets you have a lot of control with the associated difficulty of the learning curve.