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.
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;
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: