BookmarkSubscribeRSS Feed
_Hopper
Obsidian | Level 7

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.

 

_Hopper_2-1738679279596.png

 

 

 

 

 

 

14 REPLIES 14
PaigeMiller
Diamond | Level 26

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;

 

--
Paige Miller
_Hopper
Obsidian | Level 7

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
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
_Hopper
Obsidian | Level 7

Test data

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
_Hopper
Obsidian | Level 7
The Location values just have a single word format - no carriage return. My error.
_Hopper
Obsidian | Level 7

Using Cars as follows:

 

ds escapechar = "~";
options orientation = landscape leftmargin = 0.2in rightmargin = 0.2in options missing = '';
 
data cars;
set sashelp.cars;
msrp2 = round(msrp/1000,.1);
run;
 
proc sql;
create table origin as
select
strip(origin) as start,
strip(origin)||"~n( N = "||strip(put(count(origin),8.))||")" as label,
    "origin" as fmtname,
"C" as type
from
cars
group by
origin;
quit;
 
proc format cntlin = origin;
run;
 
ods rtf file = "C:\Users\xxx\test.rtf" style = ourstyle;
proc report data = cars split='/';
    column ("Make~n     Other Stuff" make ) ("Type~n     Other Stuff" type) origin,drivetrain,msrp2 dummy;
    define make    / '' group order = internal;
    define type / '' group order = internal ;
define drivetrain/'' across order =  internal;
    define origin/ '' across order = internal format = $origin.;
    define msrp2/mean ' ' format = f8.2;
    define dummy/ noprint;
run;
ods _all_ close;

 

 

 

 

Generates this output with the formatted value above the others due to the carriage returns. 

Can it be aligned?

_Hopper_0-1738687077703.png

 

Cynthia_sas
SAS Super FREQ

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:

Cynthia_sas_0-1738690345093.png

 

  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

_Hopper
Obsidian | Level 7

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?

Cynthia_sas
SAS Super FREQ

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

Tom
Super User Tom
Super User

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.

Cynthia_sas
SAS Super FREQ

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:

Cynthia_sas_0-1738721170825.png

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

Tom
Super User Tom
Super User

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;
Ksharp
Super User
Try to change the position of VALUE:
Treatment,Location,Value
----->
Value,Treatment,Location

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 14 replies
  • 503 views
  • 0 likes
  • 5 in conversation