How to report summary data with fewer columns than detail

[deleted_user]

I have a report requirement that shows Summary and Detail infomation. The requirement is that the summary level contains many columns fewer than the detail. The user wants the Summary information (with its own header) followed by its detail information below it (with it's own header).
Currently I'm doing this manually in Excel but wanted to try to use proc report.

Below is code from the Revenue table. The first report has, in this example, one less column than the detail report below:

So for example the summary row would appear as:
[summary]
Origin RevBusiness RevEcon
ANC 123480 325791

[detail]
Origin destination RevBusiness RevEcon
ANC RDU 123480 325791



proc report data=SASUSER.REVENUE;
col Origin RevBusiness RevEcon;

define origin/group;
define RevBusiness/ analysis sum;
define RevEcon/ analysis sum;
run;


proc report data=SASUSER.REVENUE;
col Origin dest RevBusiness RevEcon;

define origin/group;
define dest/ group 'destination';
define RevBusiness/ analysis sum;
define RevEcon/ analysis sum;
run;

[Cynthia_sas]

Patrick:
Your report program will run much better if you put the option NOWD on the PROC REPORT statement. [pre]
proc report data=sasuser.revenue nowd;
...
[/pre]
The NOWD option prevents the interactive report design window from popping up. If you are submitting your PROC REPORT in batch, this would not happen to you, but in an interactive session of SAS, the REPORT window could pop up and then no other statements would execute until the report window closed.

I'm not sure, from your description, what you want to do. Your code is going to produce 2 tables -- one with 3 report columns (the first proc) and the other table with 4 report columns (the second proc).

When PROC REPORT builds a table, it wants to put the SAME number of columns on every report row. The only exception to this rule is that output from a LINE statement will span ALL the rows. So your first report will ALWAYS have 3 columns on EVERY report row and your second report will ALWAYS have 4 columns on EVERY report row.

It is possible for PROC REPORT to put a summary line BEFORE all the detail lines on a detail report or even a grouped report with some summary lines (such as your second report). This is a powerful feature of PROC REPORT. If you were going to modify the second table to use this feature, then the value for DESTINATION would be blank on this summary row.

To change the second program in this manner, you probably want to add a BREAK BEFORE and maybe a BREAK AFTER statement to your PROC REPORT code:
[pre]
break before origin/summarize skip dol dul ;
break after origin / skip;
[/pre]
OR
[pre]
break before origin/summarize skip dol dul ;
break after origin / page;
[/pre]
However, as I said, this break line would have 4 columns on EVERY row and the column for DESTINATION would just be blank on the summary row.

To see the difference between a detail report with a summary line and a grouped report with a summary line, change the usage of DESTINATION to ORDER for one of your runs and then compare it to the output from when the usage was GROUP for DESTINATION. The difference should stand out.

cynthia