ODS Excel - Proc Report using Across option

[SASaholic629]

• I have 3 variables - Quarter (char), Bucket (char), Value (numeric)
• I want to us Proc Report to display Quarter going across, and Bucket going down
• When I do that using the code below, it is not grouping the Bucket going down, but rather duplicating Buckets, so that each value for each quarter is on its own row.

DATA Have;
INFILE DATALINES MISSOVER DELIMITER='|';
INPUT Qtr :$4. Bucket :$1. Value 8.;
DATALINES;
20Q4|A|10
20Q4|B|15
20Q4|C|20
21Q1|A|11
21Q1|B|14
21Q1|C|22
21Q2|A|12
21Q2|B|15
21Q2|C|23
;RUN;

ODS EXCEL FILE="FILEPATH/OutputFile.xlsx";

PROC REPORT DATA=Have;
TITLE 'Quarterly';
COLUMNS	Bucket Qtr,(Value);
	DEFINE Bucket	/ 'Bucket'	DISPLAY	STYLE=[CELLWIDTH=1.1in FONT_WEIGHT=BOLD];
	DEFINE Qtr		/ ' '		ACROSS ' ';
	DEFINE Value	/ 'Value'	DISPLAY	STYLE=[CELLWIDTH=1.1in];
RUN;

ODS EXCEL CLOSE;

The result looks like this:

	20Q4	21Q1	21Q2
Bucket	Value	Value	Value
A	10	.	.
B	15	.	.
C	20	.	.
A	.	11	.
B	.	14	.
C	.	22	.
A	.	.	12
B	.	.	15
C	.	.	23

But I want the result to look like this (including creating a total row for each quarter):

	20Q4	21Q1	21Q2
Bucket	Value	Value	Value
A	10	11	12
B	15	14	15
C	20	22	23
Total	45	47	50

Is this possible to do, so that when new quarters are added to the data, it will automatically add a new column for the new quarter? And, of course, group them all on the same row for the same bucket.

[data_null__ · Accepted solution]

It is not grouping on bucket because you defined it as DISPLAY.

PROC REPORT DATA=Have;
TITLE 'Quarterly';
COLUMNS	Bucket Qtr,(Value);
	DEFINE Bucket	/ 'Bucket'	group	STYLE=[CELLWIDTH=1.1in FONT_WEIGHT=BOLD];
	DEFINE Qtr		/ ' '		ACROSS ' ';
	DEFINE Value	/ 'Value'	sum	STYLE=[CELLWIDTH=1.1in];
   rbreak after / summarize;
RUN;