Hi All
Need some help with proc report output. If you submit the code below, you will notice that the report contains an empty space for week three on monday. Is there any way to prevent this by making sure that the tuesday rows are printed on the first row of week three?
many thanks
John
UPDATE: Code simplified.
DATA WORK.theatre_dim;
LENGTH THEATRE $3. WEEK $1. DAY SESSION $10. REVENUE 8.;
INFILE DATALINES4 DLM=';' MISSOVER DSD ;
INPUT THEATRE :$3. WEEK :$1. DAY :$10. SESSION :$10. REVENUE :BEST12. ;
DATALINES4;
TH7;3;Monday;SESSION1;4000
TH7;3;Monday;SESSION2;4000
TH7;3;Monday;SESSION7;4000
TH7;3;Tuesday;SESSION3;2000
TH7;3;Tuesday;SESSION4;2000
;;;;
run;
proc report data=theatre_dim nowd ;
column THEATRE WEEK DAY, ( SESSION REVENUE );
define THEATRE / group;
define WEEK / group;
define session / display;
define DAY / ACROSS ;
define REVENUE / sum;
run;
Hi:
I'm not at a point where I can run your code, but I suspect it has something to do with the fact that your UTILISATION variable is DISPLAY along with the fact that you have presummarized the data and done ordering in your SQL query by DATE THEATRE, and SESSION and then use GROUP for THEATRE and WEEK in your DEFINE statements. This means that you will get a unique row for every WEEK within THEATRE. Which means, for example, that summarized values belonging to WEEK 3 for Tuesday will NOT "move up" to an empty WEEK 2 report row or that WEEK 4 report info will "move up" to the WEEK 3 report row. If WEEK is not relevant to your report grouping and ordering, then you should take it off the report. If WEEK is relevant to your report grouping, then PROC REPORT will treat each week as being unique.
You might want to open a track with Tech Support for more help with your report.
cynthia
Hi Cynthia,
I'vwe modified my code to demonstrate my issue more clearly. I have no expectation of summarised values "moving up" to the wrong week, but do expect the rows for a paticular week to start on the same row.
As you see in the table below, Tuesdays records always begin on the last record for Monday.
| DAY | ||||
| Monday | Tuesday | |||
THEATRE | WEEK | SESSION | REVENUE | SESSION | REVENUE |
TH7 | 3 | SESSION1 | 4000 |
| . |
|
| SESSION2 | 4000 |
| . |
|
| SESSION7 | 4000 | SESSION3 | 2000 |
|
|
| . | SESSION4 | 2000 |
I would have expected SESSION3 on Tuesday to the on the same row as SESSION1 on Monday as shown below.
| DAY | ||||
| Monday | Tuesday | |||
THEATRE | WEEK | SESSION | REVENUE | SESSION | REVENUE |
TH7 | 3 | SESSION1 | 4000 | SESSION3 | 2000 |
|
| SESSION2 | 4000 | SESSION4 | 2000 |
|
| SESSION7 | 4000 |
|
|
Is there any way to achieve my desired output?
thanks
John
Hi:
But this is the same issue. In the original example, you had UTILISATION with DISPLAY usage under DAY. Now you have SESSION with DISPLAY usage under DAY. PROC REPORT just doesn't look at the VALUES for SESSION when it's filling the report rows, unless you make SESSION an ORDER or GROUP usage item and you can't have an ORDER or GROUP item under an ACROSS, so you'd have to move SESSION out of the ACROSS scenario. Screenshots attached. #2 output created with program below (uses your revised data) #1 is your original report and #3 shows the collapsing that happens when only WEEK is on the report row.
cynthia
ods listing close;
ods html file='c:\temp\output\across_ques.html' style=analysis;
proc report data=theatre_dim nowd ;
title '1) original code session "under" day';
column THEATRE WEEK DAY, ( SESSION REVENUE );
define THEATRE / group;
define WEEK / group;
define session / display;
define DAY / ACROSS ;
define REVENUE / sum;
run;
proc report data=theatre_dim nowd ;
title '2) session as group and not under across variable';
column THEATRE WEEK session DAY, ( REVENUE );
define THEATRE / group;
define WEEK / group;
define session / group;
define DAY / ACROSS ;
define REVENUE / sum;
rbreak after / summarize;
run;
proc report data=theatre_dim nowd ;
title '3) WEEK as group only';
column THEATRE WEEK DAY, ( REVENUE );
define THEATRE / group;
define WEEK / group;
define DAY / ACROSS ;
define REVENUE / sum;
run;
ods _all_ close;
your report looks so strange.
I think the only way to get this report is to use data step to re-form data structure.
Ksharp
this is a strange layout.
as Ksharp suggests, a better result can be created by re-shaping the data.
It seems equivalent to an M:N merge of a data step where you do not want the sql-type cartesian join, but just what the DATA STEP MERGE delivers.
Here is some tested code and the results I get from PROC PRINT - which I assume can be adapted for PROC REPORT
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.