BookmarkSubscribeRSS Feed
forumsguy
Fluorite | Level 6

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

5 REPLIES 5
ballardw
Super User

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.

Cynthia_sas
SAS Super FREQ

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

forumsguy
Fluorite | Level 6

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

Cynthia_sas
SAS Super FREQ

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

Cynthia_sas
SAS Super FREQ

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 950 views
  • 0 likes
  • 3 in conversation