The SAS Output Delivery System and reporting techniques

Difficulty with proc report output

Reply
SAS Employee
Posts: 8

Difficulty with proc report output

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;

SAS Super FREQ
Posts: 8,868

Difficulty with proc report output

Posted in reply to johnc_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

SAS Employee
Posts: 8

Difficulty with proc report output

Posted in reply to Cynthia_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

SAS Super FREQ
Posts: 8,868

Re: Difficulty with proc report output

Posted in reply to johnc_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;


show_session.jpgsession_vs_week.jpg
Super User
Posts: 10,041

Re: Difficulty with proc report output

Posted in reply to johnc_sas

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

Valued Guide
Posts: 2,177

Re: Difficulty with proc report output

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

Attachment
Attachment
Ask a Question
Discussion stats
  • 5 replies
  • 226 views
  • 0 likes
  • 4 in conversation