topic Difficulty with proc report output in ODS and Base Reporting

Difficulty with proc report output

johnc_sas — Thu, 10 Nov 2011 14:59:13 GMT

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;

Difficulty with proc report output

Cynthia_sas — Fri, 11 Nov 2011 18:52:56 GMT

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

Difficulty with proc report output

johnc_sas — Mon, 14 Nov 2011 11:42:52 GMT

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

Re: Difficulty with proc report output

Cynthia_sas — Mon, 14 Nov 2011 16:00:45 GMT

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;

Re: Difficulty with proc report output

Ksharp — Tue, 15 Nov 2011 03:31:22 GMT

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

Re: Difficulty with proc report output

Peter_C — Thu, 17 Nov 2011 17:50:33 GMT

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