The SAS Output Delivery System and reporting techniques

Column Headers listed in more than one row

Reply
Frequent Contributor
Posts: 83

Column Headers listed in more than one row

I would like to be able to list my column headers in more than one row. For example, I have 20 column headers and I would like the first 10 displayed in the first row of the report and the last 10 to be displayed in the second row of the report.
The first row lists employee information and the second row would list financial information about the employee.
Is this possible?

Thanks
SAS Super FREQ
Posts: 8,866

Re: Column Headers listed in more than one row

Hi:
Without a bit more information, it is hard to come up with an answer to your question. Is your data all in one table or do you have the employee information in one table and the financial information in another table??

Your question is about column headers -- having more than one column header/label in a row -- but what about the data values??? would you want to see the data values occupy only 1 row, too?? Essentially, you would "stack" headers and data values??

What is your destination of choice??? HTML, RTF, PDF???

Normally, PROC PRINT or PROC REPORT would give you something like this (showing only a few rows from SASHELP.CLASS):
[pre]
Name Sex Age Height Weight
Robert M 12 64.8 128
Thomas M 11 57.5 85
[/pre]

If, on the other hand, you wanted to "stack" some of the column headers and values, like this:
[pre]
Name Height
Sex Age Weight
Robert 12 64.8
M 128.0

Thomas 11 57.5
M 85.0
[/pre]

It is possible, but really depends on how your data is structured, what your procedure of choice is, whether you're willing to "pre-process" the data to stack values, what your destination of interest is, etc, etc.

cynthia
Frequent Contributor
Posts: 83

Re: Column Headers listed in more than one row

Posted in reply to Cynthia_sas
Cynthia,

The data is all in one table. And Yes I would like to "stack" the headers and data values by employee.
I would also like the output to be PDF.

Please let me know if I can provide more information to be helpful.

Thanks
SAS Super FREQ
Posts: 8,866

Re: Column Headers listed in more than one row

Hi:
By far, the easiest thing to do would be to use BY group processing to print 1 observation per employee, wrapped across multiple rows. This doesn't exactly give you the look you want, but it does get one employee's observation as a "unit". With ODS PDF options, such as STARTPAGE=NO, you can put multiple employee observations on one page.

The example below shows that method (#1) using selected observations from SASHELP.CLASS.

Your other alternatives are more programming intensive and depending on your comfort level, may or may not be worth the effort.

Method 2: Pre-process the original data to concatenate the employee information with the financial information and put the appropriate "line feed" instruction between the two pieces of information so it is stacked in the cell as you want. Change the labels as needed for your stacked cells. Then use PROC PRINT or PROC REPORT to send this new version of the data to ODS PDF.

Method 3: Use a custom table template with a DATA _NULL_ program to write to ODS PDF. The PROC TEMPLATE syntax for table templates has a method by which you can stack multiple variables into one cells.

cynthia
[pre]
** Method 1 -- print one table per person;
** make some extra variables so there is something to wrap;
** around to the next row.;

ods listing close;

data class1;
set sashelp.class;
where age le 12;
array xx $9 xvar1-xvar20;
do i = 1 to 20 by 1;
xx(i) = cats(put(i,2.0),'--',substr(name,1,1),'--',put(i,2.0));
end;
run;

proc sort data=class1 out=class1;
by name;
run;

options leftmargin=.25in rightmargin=.25in topmargin=.25in
bottommargin=.25in orientation=portrait center;

ods pdf file='c:\temp\Method1.pdf' startpage=no notoc;

proc report data=class1 nowd
style(report)={outputwidth=7.5in} ;
by name;
column sex age height weight xvar:;
run;

ods _all_ close;
[/pre]
Frequent Contributor
Posts: 83

Re: Column Headers listed in more than one row

Posted in reply to Cynthia_sas
Cynthia,

Thanks so much for that information. In Method 2 you mentioned I can change the labels as need for the stacked cells. How can I do this? For example, the first column will be stacked with dept info / job class info. How do I change the column name to:
dept
job_class

Instead of just:
dept

I will be performing this in E.G.
SAS Super FREQ
Posts: 8,866

Re: Column Headers listed in more than one row

Hi:
Even in EG, you will need to submit code. I do not believe this will be a point and click effort.

You will need, in PROC REPORT (or PROC PRINT) to define a SPLIT character (such as '*' in my code) and then manually build the labels for the "new" concatenated column (as in 'Name*Sex' or 'Height*Weight'). The ODS ESCAPECHAR function {newline} is used with a declared escape character to insert a line feed between the 2 values that are being concatenated. Since you have to do all the work in the DATA step program to concatenate the values together (see how I make the NAME_SEX and HT_WT variables?), that's why I said the BY group approach was by far, the easiest (in my opinion).

cynthia
[pre]
** Method2: pre-process the data and add a line feed;
** for the values that will be "stacked";

data class2;
length name_sex ht_wt $25;
set sashelp.class;
where age le 12;
name_sex = catt(name,'~{newline 1}',sex);
ht_wt = catt(put(height,5.1),'~{newline 1}',put(weight,5.2));
run;

ods pdf file='c:\temp\method2.pdf' notoc;
ods escapechar = '~';

proc report data=class2 nowd split='*';
column name_sex age ht_wt;
define name_sex / display 'Name*Sex'
style(column)={just=l};
define age / display '*Age';
define ht_wt/display 'Height*Weight'
style(column)={just=r};
run;
ods _all_ close;
[/pre]
Super User
Posts: 10,035

Re: Column Headers listed in more than one row

Or

proc report data=class2 nowd;

     column ( 'Name' name_sex) age ht_wt;

     define name_sex / display  'Sex'

         style(column)={just=l};

     define age / display '*Age';

     define ht_wt/display 'Height*Weight'

         style(column)={just=r};

  run;

Kshar

Ask a Question
Discussion stats
  • 6 replies
  • 531 views
  • 0 likes
  • 3 in conversation