The SAS Output Delivery System and reporting techniques

Report with one header for subgroup

Reply
Frequent Contributor
Posts: 85

Report with one header for subgroup

Hi there,

I want to prepare report like

insurance
(1)
insurance
(2)
insurance
(3)
insurance
(4)
written
GrossA1A2A3A4
acceptedB1B2
shareC1
NetD1=A1+B1-C1
Premiums
GrossA1A
acceptedB1A
shareC1A
NetD1A=A1A+B1A-C1A
Claims
GrossE1
acceptedF1
shareG1
NetH1=E1+F1-G1
Expenses
Recycle
GrossE1A
acceptedF1A
shareG1A
Total NetH1A=E1A+F1A-G1A
Investment
GrossE1B
acceptedF1B
shareG1B
Total NetH1B=E1B+F1B-G1B

I have used following proc tabulate code to produce the output.. But my concern is about header Expenses and last entry for expenses subgroup(Total Net).

PROC TABULATE DATA =Temp

                       FORMAT=commax17.2 STYLE={font=("Helvetica median",8pt,Normal)};

                       CLASS ClassrankTP ClassrankNM ClassrankCAT ClassrankME / MISSING S=[background=&bgcol font=("Helvetica median",8pt,Normal)];

                       CLASSLEVEL ClassrankTP ClassrankNM ClassrankCAT ClassrankME/ S=[background=&bgcol  font=("Helvetica median",8pt,Bold)];

                       VAR cvg_1 / S=[font=("Helvetica median",8pt,Normal)];

                       FORMAT ClassrankTPclassNLTPCode. ClassrankME classNLMeasureCode.ClassrankNM classNLCoverCode. ClassrankCATclassCategoryCode.;

                       TABLES ClassrankCAT =  {LABEL=' ' S=[CellWidth=150 foreground=black  font=("Helvetica median",8pt,bold)]} *

                                ClassrankME=' ',

                                ClassrankTP=' ' *

                                ClassrankNM=' '*

                                cvg_1=' ' *

                                sum=''/ BOX={LABEL='' S=[ background=black]};

               RUN;

Any Help would be really appreciated

Super User
Posts: 11,343

Re: Report with one header for subgroup

Posted in reply to forumsguy

It would be helpful to explain what concerns you have.

If the values for the Share levels were negative I think you would be able to get what you want with something like

table classrankcat *(classrankMe All),

but tabulate doesn't suport subtraction in any form that I'm aware of.

SAS Super FREQ
Posts: 8,864

Re: Report with one header for subgroup

Oh, good eyes! I didn't notice that minus sign....no, that is an indication that PROC REPORT and a COMPUTE block is needed. Unless you preprocess  the data so all the appropriate variables are negative.

cynthia

Frequent Contributor
Posts: 85

Re: Report with one header for subgroup

Posted in reply to Cynthia_sas

My Concern is not the calculation. My data has all the calculations required. If you look at the output, you will find out that written and premiums have last section as Net and all premiums have last section as Total Net and just above first expense there is heading as Expenses. I am worried about only these two parts ...Thanks for all the suggestions

SAS Super FREQ
Posts: 8,864

Re: Report with one header for subgroup

Posted in reply to forumsguy

Hi:

  I don't actually SEE any output. I see a model of possible output with Excel-like column names where normally I would see numbers. Without any data, I can't run your TABULATE to see what you're seeing. And, yes, I see Total Net and Net, but I assumed those were coming from your data, since you do not have any ALL references in your TABLE statement.

cynthia

SAS Super FREQ
Posts: 8,864

Re: Report with one header for subgroup

Posted in reply to forumsguy

Does your TABULATE work? If not, what about the output is wrong? Your explanation with the E1, E1A, E1B, etc, seems to me to indicate that you are somehow equating TABULATE calculated cells like Excel cells -- which they are NOT equivalent in concept. Seeing your data would be more helpful than seeing the Excel-y names for the cells. Generally, if you want subtotals, such as you show, you need to use ALL in your ROW or COLUMN dimension. Something like this (the SUM statistic doesn't make sense for these variables):

proc tabulate data=sashelp.class;

class sex age;

var height;

table (sex all='Gender Total') (age all="Age  Total"),

          height*(min mean max);

run;

...will put a table and total for SEX above a table and total for AGE.

cynthia

Ask a Question
Discussion stats
  • 5 replies
  • 387 views
  • 0 likes
  • 3 in conversation