I have a report template set up as follows
I have grouping columns which require carriage returns but further right in the report, I have formatted nested across columns. It works, but since
My current proc report code looks like this
Proc report data = xxxx;
column (" Label ~n more label" A ) ("Label ~n more label" B) Treatment,Location,Value dummy;
define A / '' group order = internal;
define B / ' ' group order = internal;
define treatment / '' across order = internal;
define location/ '' across order = internal;
define Value/group '';
define dummy/ noprint;
run;
Treatment is a formatted value with the population count so it has the form "Treatment 1~n(N = xx) - Treatment 2 is the same
Location is also a formatted numeric value (Left or Right)
Value contains the reported values.
The issue is that I have extra space in the across rows because of the carriage returns in A, B and the treatment columns when doing this (see below). Is there any way to get the labels for the treatments pushed down one row? I prefer not to use PROC TRANSPOSE for this operation as treatments could be added later and I need something flexible. The report runs as expected with no errors, warnings or undesired messages returned.
It's always better to provide sample data (it could be fake data, as long as it illustrates the problem), as working SAS data step code (examples and instructions) and not as Excel files and not as copy and paste from Excel or elsewhere.
As an alternative, illustrate the problem you are having with packaged data sets that everyone has such as SASHELP.CARS or SASHELP.CLASS or any other data set in library SASHELP.
We consider this to be mandatory in your future posts.
Here is my solution using SASHELP.CARS
proc report data = sashelp.cars split='~';
column (" " make ) type,origin,msrp dummy;
define make / 'MAKE' group order = internal;
define type / '' across order = internal;
define origin/ '' across order = internal;
define msrp/mean 'MSRP';
define dummy/ noprint;
run;
Here is what the sample data would look like:
Text value | Text value | Numeric | Numeric | Numeric converted to text. |
Label has carrriage return in it | Label has carrriage return in it | Formatted values Treatment ~n(N = xx) | Formatted values Treatment ~n(N = xx) | |
Column 1 | Column 2 | Treatment | Location | Value |
A | A | 1 | 1 | 10 |
A | A | 1 | 2 | 11 |
A | A | 2 | 1 | 12 |
A | A | 2 | 2 | 13 |
A | B | 1 | 1 | 14 |
A | B | 1 | 2 | 15 |
A | B | 2 | 1 | 16 |
A | B | 2 | 2 | 17 |
A | C | 1 | 1 | 18 |
A | C | 1 | 2 | 19 |
A | C | 2 | 1 | 20 |
A | C | 2 | 2 | 21 |
A | D | 1 | 1 | 22 |
A | D | 1 | 2 | 23 |
A | D | 2 | 1 | 24 |
A | D | 2 | 2 | 25 |
Thanks, but I can't use data in that format. I gave specific instructions about how to provide data, and also said not to provide data in Excel files and not to provide data as copy/paste from Excel.
Test data
It would help if you read my instructions for providing data, you have now provided data in two different ways that I said NOT to do, and you haven't followed the examples and instructions in my post.
Using Cars as follows:
Generates this output with the formatted value above the others due to the carriage returns.
Can it be aligned?
Hi:
Using some fake data, it is possible to insert your line feeds where you want them without using ODS ESCAPECHAR. PROC REPORT has a default SPLIT character that it allows you to use for headers. The default SPLIT character is a slash / and the advantage of using the default is that it works in all destinations and works without using ODS ESCAPECHAR. And the older style ESCAPECHAR that you used in your code was changed in SAS version 9.2 so although the older syntax still works, I'd either recommend moving to the newer ESCAPECHAR syntax for inserting a line break in the header or just switch to using the default SPLIT character. Here's the code I used to make fake data and generate 2 examples:
I used macro variables for the N= values and just gave them values of 111 and 222 in my %LET statements. In production, of course, you would need to find out the N before creating the format or else write a little routine to make the macro variables to hold each count. I changed your code just a bit. I didn't understand why you needed the dummy variable in the COLUMN statement, so I got rid of it.
Hope this helps explain a bit more about PROC REPORT and how to insert line breaks into HEADER rows.
Cynthia
But this does not solve the underlying issue which is getting the column headers where across is used on the lowest level possible. Is this type of result not possible in PROC REPORT when using the across syntax with returns in the headers?
Hi:
I guess I don't understand what you mean by "ACROSS used on the lowest level possible". Can you tell me what you consider the lowest level possible to be? To me you have VALUE nested as the data cell for each unique combination of LOCATION within TREATMENT. So to me, I would say that LOCATION is the lowest level of ACROSS variable. Do you mean that you want N=?? to be on a new line for every unique combination of LOCATION within each TREATMENT?
Cynthia
It seems to have eliminated the blank header row in the across columns.
How was that accomplished?
Note: It is impossible to play with your examples when you only post them as photographs. If you think the code it too intrusive in your posting use the Insert a Spoiler Tag icon to hide the code until the user wants to see it.
Hi Tom:
Here's the full code. Sorry, my bad. I was planning to color code and annotate in the screen shot but then got called away and so I just posted it without much extra commenting.
data fakedata;
infile datalines dlm=',';
input Col1 $ Col2 $ Treatment Location Value;
datalines;
A, A, 1, 1, 1
A, A, 1, 2, 2
A, A, 2, 1, 3
A, A, 2, 2, 4
A, B, 1, 1, 5
A, B, 1, 2, 6
A, B, 2, 1, 7
A, B, 2, 2, 8
;
run;
** need to assign values to the macro variables for N/Treatment before this proc format runs;
** or else hardcode the values in the format;
** or else use a different technique to get the headers;
%let t1_cnt = 111;
%let t2_cnt = 222;
proc format;
value treatf 1="Treatment 1/N=&t1_cnt"
2="Treatment 2/N=&t2_cnt";
value locf 1="Location 1"
2="Location 2";
run;
title;
ods escapechar='~';
Title '1) Using ESCAPECHAR in column header for Col1 and Col2';
title2 'But use SPLIT default character for Treatment labels';
Proc report data = fakedata
style(header)={vjust=b};
column (" Label ~n more label" Col1 ) ("Label ~n more label" Col2) Treatment,Location,Value;
define Col1 / '' group order=internal;
define Col2 / '' group order=internal;
define treatment / '' across order=internal f=treatf.;
define location/ '' across order=internal f=locf.;
define Value/sum '';
run;
Title '2) Using default SPLIT for line feed in header';
title2 'adding N= as second line for Treatment';
Proc report data = fakedata
style(header)={vjust=b};
column ('Label/more label' Col1) ('Label/more label' Col2) Treatment,Location,Value;
define Col1 / '' group order=internal;
define Col2 / '' group order=internal;
define treatment / '' across order=internal f=trtnf.;
define location/ '' across order=internal f=locf.;
define Value/sum '';
run;
title;
Notice that all of the column headers in the DEFINE statements are turned off with the empty or null '' as the label -- that means the spanning headers in the COLUMN statement will be used for Col1 and Col2 and the VALUES for TREATMENT and LOCATION will be visible for the OTHER HEADERS. Example #3 blanks all headers in the DEFINE, but example #4 only blanks some headers:
Here's the code that used the same FAKEDATA file to generate those outputs:
Title '3) show ALL headers including spanning Headers';
Proc report data = fakedata
style(header)={vjust=b};
column ('Label/more label' Col1) ('Label/more label' Col2) Treatment,Location,Value;
define Col1 / 'Col1' group order=internal;
define Col2 / 'Col2' group order=internal;
define treatment / 'Treatment' across order=internal f=trtnf.;
define location/ 'Location' across order=internal f=locf.;
define Value/sum 'Value';
run;
Title '4) Only blank out some headers';
Proc report data = fakedata
style(header)={vjust=b};
column ('Label/more label' Col1) ('Label/more label' Col2) Treatment,Location,Value;
define Col1 / 'Col1' group order=internal;
define Col2 / 'Col2' group order=internal;
define treatment / '' across order=internal f=trtnf.;
define location/ '' across order=internal f=locf.;
define Value/sum '';
run;
title;
Cynthia
So the change was to remove the LABEL of the GROUP variables in the DEFINE statements and instead add the header into the COLUMN statement?
So this code has the empty cells:
proc report data=sashelp.class;
column Age sex,weight;
define age / group ;
define sex / across;
define weight / mean ' ';
run;
But this one does not becuase the label is removed from AGE and instead a string constant is added to the COLUMN statement to replace it.
proc report data=sashelp.class;
column ('Age' age) sex,weight;
define age / group ' ';
define sex / across;
define weight / mean ' ';
run;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.