Hi there,
I want to prepare report like
insurance (1) | insurance (2) | insurance (3) | insurance (4) | |
written | ||||
Gross | A1 | A2 | A3 | A4 |
accepted | B1 | B2 | … | |
share | C1 | |||
Net | D1=A1+B1-C1 | |||
Premiums | ||||
Gross | A1A | |||
accepted | B1A | |||
share | C1A | |||
Net | D1A=A1A+B1A-C1A | |||
Claims | ||||
Gross | E1 | |||
accepted | F1 | |||
share | G1 | |||
Net | H1=E1+F1-G1 | |||
Expenses | ||||
Recycle | ||||
Gross | E1A | |||
accepted | F1A | |||
share | G1A | |||
Total Net | H1A=E1A+F1A-G1A | |||
Investment | ||||
Gross | E1B | |||
accepted | F1B | |||
share | G1B | |||
Total Net | H1B=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
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.
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
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
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
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
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.