Difficulty with proc report output

[johnc_sas]

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;

[Cynthia_sas]

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

[johnc_sas]

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

[Cynthia_sas]

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;

[Ksharp]

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

[Peter_C]

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