Report with 3 header lines

[Sven4IBM]

I've a cross table result and my customer wish that the sum of amount (first line) columns shall stay on the top over the header (second line). The header shall have in the third line an description of the columns.

Any ideas ?

[ballardw]

Example starting data

The code you currently use to create the table

Describe or provide and example of what this should look like if you have more than one value for Kundennummer and/ or Name.

If the values you show are actually calculated in a report procedure you may to do a separate step to summarize the data prior to the table creation to have the value for such a use.

[Sven4IBM]

I've created a simple example. In the Attachement you find the result of my wish. The proc tabulate cannot work with many class variables. I would take the proc Report but I don't know how could program my result in the Excel sheet.

Line 1 shall stay the sum of the columns. In column G shall stay the sum of lines (E:F). The block H till J is the same like E till G but only in Euro.

At the End I Need an Excel file.

DATA temp; infile cards delimiter=',';
input subject name $ number currency $ loan $ txt $ amount;
CARDS;
68832,Alice,100240,usd,046200,Nostro,175
68832,Alice,100250,eur,046200,Nostro,150
68832,Alice,100240,usd,046300,Nostro,275
68832,Alice,100250,eur,046300,Nostro,250
68834,Thomas,100340,usd,046300,Nostro,250
68834,Thomas,100350,eur,046300,Nostro,225
68835,Thomas,100340,usd,046300,Nostro,250
68835,Thomas,100350,eur,046300,Nostro,225
;
RUN;

PROC TABULATE
DATA=WORK.TEMP;	
	VAR amount;
	CLASS subject / ORDER=UNFORMATTED MISSING;
	CLASS name / ORDER=UNFORMATTED MISSING;
	CLASS number / ORDER=UNFORMATTED MISSING;
	CLASS currency / ORDER=UNFORMATTED MISSING;
	CLASS loan / ORDER=UNFORMATTED MISSING;
	CLASS txt / ORDER=UNFORMATTED MISSING;
	TABLE /* Zeilendimension */
ALL={LABEL="Summe (ALL)"}*
  Sum={LABEL=""} 
subject*
  name*
    number*
      currency*
        Sum={LABEL=""},
/* Spaltendimension */
loan={LABEL=""}*
  txt={LABEL=""}*
    amount={LABEL="OC"} 		;
	;
RUN;

[Sven4IBM]

I've created a proc Report, but for 'Loan' i've got all 'txt' values. But I've only for one loan one txt.

DATA temp; infile cards delimiter=',';
input subject name $ number currency $ loan $ txt $ amount;
CARDS;
68832,Alice,100240,usd,046200,Loro,175
68832,Alice,100250,eur,046200,Loro,150
68832,Alice,100240,usd,046300,Nostro,275
68832,Alice,100250,eur,046300,Nostro,250
68834,Thomas,100340,usd,046300,Nostro,250
68834,Thomas,100350,eur,046300,Nostro,225
68835,Thomas,100340,usd,046300,Nostro,250
68835,Thomas,100350,eur,046300,Nostro,225
;
RUN;

proc report data=WORK.temp
nowindows missing headline spanrows split='*'
;
	column subject name number currency loan, txt, amount
;

define subject / group display 'subject '
  style(header)={just=left BACKGROUND=#4F81BD}
  style(column)={font_face=Arial font_size=2};
define name / group display 'name'
  style(header)={just=left BACKGROUND=#4F81BD}
  style(column)={font_face=Arial font_size=2};
define number / group display 'number'
  style(header)={just=left BACKGROUND=#4F81BD}
  style(column)={font_face=Arial font_size=2};
define currency / group display 'currency'
  style(header)={just=left BACKGROUND=#4F81BD}
  style(column)={font_face=Arial font_size=2};
define loan / across ''
  style(header)={just=left BACKGROUND=#4F81BD}
  style(column)={font_face=Arial font_size=2};
define txt / across  ''
  style(header)={just=left BACKGROUND=#4F81BD}
  style(column)={font_face=Arial font_size=2};
define amount / 'Amount in OC'
  style(header)={just=left BACKGROUND=#4F81BD}
  style(column)={font_face=Arial font_size=2 TAGATTR='format:#,##0.00;[Red](#,##0.00);[Blue]0.00;'};
run;

[Sven4IBM]

I've found the Options. It is calling nozero.

define txt / across nozero ''
  style(header)={just=left BACKGROUND=#4F81BD}
  style(column)={font_face=Arial font_size=2};

But I don't understand why I've got single rows instead sum of any Groups.

The sum option has no effekt (define amount / sum).

DATA temp; infile cards delimiter=',';
input subject name $ number currency $ loan $ txt $ amount;
CARDS;
68832,Alice,100240,usd,046200,Loro,175
68832,Alice,100250,eur,046200,Loro,150
68832,Alice,100240,usd,046300,Nostro,275
68832,Alice,100250,eur,046300,Nostro,250
68834,Thomas,100340,usd,046300,Nostro,250
68834,Thomas,100350,eur,046300,Nostro,225
68835,Thomas,100340,usd,046300,Nostro,250
68835,Thomas,100350,eur,046300,Nostro,225
;
RUN;

proc report data=WORK.temp
nowindows missing headline spanrows split='*'
;
	column subject name number currency loan, txt, amount
;

define subject / group display 'subject '
  style(header)={just=left BACKGROUND=#4F81BD}
  style(column)={font_face=Arial font_size=2};
define name / group display 'name'
  style(header)={just=left BACKGROUND=#4F81BD}
  style(column)={font_face=Arial font_size=2};
define number / group display 'number'
  style(header)={just=left BACKGROUND=#4F81BD}
  style(column)={font_face=Arial font_size=2};
define currency / group display 'currency'
  style(header)={just=left BACKGROUND=#4F81BD}
  style(column)={font_face=Arial font_size=2};
define loan / across nozero ''
  style(header)={just=left BACKGROUND=#4F81BD}
  style(column)={font_face=Arial font_size=2};
define txt / across nozero ''
  style(header)={just=left BACKGROUND=#4F81BD}
  style(column)={font_face=Arial font_size=2};
define amount / sum 'Amount in OC'
  style(header)={just=left BACKGROUND=#4F81BD}
  style(column)={font_face=Arial font_size=2 TAGATTR='format:#,##0.00;[Red](#,##0.00);[Blue]0.00;'};
run;

PROC TABULATE
DATA=WORK.TEMP
	
	;
	
	VAR amount;
	CLASS subject /	ORDER=UNFORMATTED MISSING;
	CLASS name /	ORDER=UNFORMATTED MISSING;
	CLASS number /	ORDER=UNFORMATTED MISSING;
	CLASS currency /	ORDER=UNFORMATTED MISSING;
	CLASS loan /	ORDER=UNFORMATTED MISSING;
	CLASS txt /	ORDER=UNFORMATTED MISSING;
	TABLE /* Zeilendimension */
subject*
  name*
    number*
      currency,
/* Spaltendimension */
loan*
  txt*
    amount*
      Sum 		;
	;

RUN;