topic Re: Using across in PROC REPORT with nesting and labels in SAS Procedures

Using across in PROC REPORT with nesting and labels

_Hopper — Tue, 04 Feb 2025 14:29:02 GMT

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.

Re: Using across in PROC REPORT with nesting and labels

PaigeMiller — Tue, 04 Feb 2025 14:49:40 GMT

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;

Re: Using across in PROC REPORT with nesting and labels

_Hopper — Tue, 04 Feb 2025 15:00:07 GMT

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

Re: Using across in PROC REPORT with nesting and labels

PaigeMiller — Tue, 04 Feb 2025 15:04:08 GMT

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.

Re: Using across in PROC REPORT with nesting and labels

_Hopper — Tue, 04 Feb 2025 15:04:21 GMT

The Location values just have a single word format - no carriage return. My error.

Re: Using across in PROC REPORT with nesting and labels

_Hopper — Tue, 04 Feb 2025 15:07:14 GMT

Test data

Re: Using across in PROC REPORT with nesting and labels

PaigeMiller — Tue, 04 Feb 2025 15:10:22 GMT

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.

Re: Using across in PROC REPORT with nesting and labels

_Hopper — Tue, 04 Feb 2025 16:42:07 GMT

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?

Re: Using across in PROC REPORT with nesting and labels

Cynthia_sas — Tue, 04 Feb 2025 17:36:25 GMT

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

Re: Using across in PROC REPORT with nesting and labels

_Hopper — Tue, 04 Feb 2025 17:43:20 GMT

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?

Re: Using across in PROC REPORT with nesting and labels

Tom — Tue, 04 Feb 2025 17:48:16 GMT

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.

Re: Using across in PROC REPORT with nesting and labels

Cynthia_sas — Wed, 05 Feb 2025 02:17:03 GMT

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

Re: Using across in PROC REPORT with nesting and labels

Cynthia_sas — Wed, 05 Feb 2025 02:16:01 GMT

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

Re: Using across in PROC REPORT with nesting and labels

Ksharp — Wed, 05 Feb 2025 02:27:53 GMT

Try to change the position of VALUE:
Treatment,Location,Value
----->
Value,Treatment,Location

Re: Using across in PROC REPORT with nesting and labels

Tom — Wed, 05 Feb 2025 02:46:47 GMT

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;