topic Report with 3 header lines in SAS Programming

Report with 3 header lines

Sven4IBM — Fri, 08 Nov 2019 07:36:19 GMT

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 ?

Re: Report with 3 header lines

ballardw — Fri, 08 Nov 2019 15:57:17 GMT

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.

Re: Report with 3 header lines

Sven4IBM — Tue, 12 Nov 2019 09:00:32 GMT

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;

Re: Report with 3 header lines

Sven4IBM — Wed, 13 Nov 2019 08:37:53 GMT

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;

Re: Report with 3 header lines

Sven4IBM — Fri, 15 Nov 2019 08:01:50 GMT

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;