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 ?
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.
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.