PROC Tabulate - Move row total, add column header, etc

[jen123]

Hello,

Below is my PROC Tabulate code. 

1.  For the columns, I would like group the B's together, C's together, etc. and create column headers.

Current Result: 

YTD14B

FYF14B

FYF15B

FYF16B

FYF17B

FYF18B

FYF5YB

Would like:

BENEFIT ($M)
YTD14	FYF14	FYF15	FYF16	FYF17	FYF18	FYF5Y

2.  There are (3) Initiatives in the Class Initiatives.  They are called Initiative A, Initiative B and Initiative C.  Each program has X number of projects.

• Current code puts the total for each program at the bottom.  How do I put the total at the top of the program?
• How can I show on the report the following order:  Program C, Program A , Program B?
• To create distinction, I would like to add a border around (1) Initiative C & Benefit, (2) Initiative C & Cost, (3) Initiative B & Benefit, etc. 

3.  Can I put (3) datasets into one report page?

4.  I have QUALIFIER in the row dimension.  The Qualifier Total = Initiative C Total + Initiative A Total + Initiative B Total.  How can I get rid of the left column that shows Qualifier but also keep the Qualifier Total row?

Version

BENEFIT ($M)

COST ($M)

YTD14

FYF14

FYF15

FYF16

FYF17

FYF18

FYF5Y

YTD14

FYF14

FYF15

FYF16

FYF17

FYF18

FYF5Y

Qualifier

Qualifier Total

Initiative C Total

Program C-1

Program C-2

Program C-3

Initiative B Total

Program B-1

Program B-2

Program B-3

Initiative A Total

Program A-1

Program A-2

Program A-3

Program

******************

PROC TABULATE

DATA=abc

     FORMAT=MILLION.

     ;

     VAR YTD14C FYF14C FYF15C FYF16C FYF17C FYF18C FYF5YC YTD14B FYF14B FYF15B FYF16B FYF17B FYF18B FYF5YB YTD14N FYF14N FYF15N FYF16N FYF17N FYF18N FYF5YN YTD14D FYF14D FYF15D FYF16D FYF17D FYF18D FYF5YD;

     CLASS Initiative /   ORDER=UNFORMATTED MISSING;

     CLASS Qualifier /ASCENDING ORDER=UNFORMATTED MISSING;

     CLASS Program / ASCENDING ORDER=UNFORMATTED MISSING;

     CLASS ProjectName /ASCENDING    ORDER=UNFORMATTED MISSING;

     CLASS Version / ORDER=UNFORMATTED MISSING;

     TABLE

     /* Row Dimension */

  Qualifier={LABEL="" STYLE(CLASSLEV)={BACKGROUND=#D6D6D6 cellwidth=50}}*(

    ALL={LABEL="Plan"}*{STYLE={FONT_WEIGHT=BOLD FONT_STYLE=ROMAN BACKGROUND=#FFFFC4}}*

      Sum={LABEL=""}

    Initiative={LABEL="" STYLE={BACKGROUND=#99CCFF}}*(

      Program={LABEL="" STYLE={BACKGROUND=#99CCFF}}*

        Sum={LABEL=""}

      ALL={LABEL="Total"}*{STYLE={FONT_WEIGHT=BOLD FONT_STYLE=ROMAN BACKGROUND=#D6D6D6}}*

        Sum={LABEL=""})),

     /* Column Dimension */

     YTD14C

     FYF14C

     FYF15C

     FYF16C

     FYF17C

     FYF18C

     FYF5YC

     YTD14B

     FYF14B

     FYF15B

     FYF16B

     FYF17B

     FYF18B

     FYF5YB

     YTD14N

     FYF14N

     FYF15N

     FYF16N

     FYF17N

     FYF18N

     FYF5YN

     YTD14D

     FYF14D

     FYF15D

     FYF16D

     FYF17D

     FYF18D

     FYF5YD

     /*Table Options */

     / MISSTEXT=""        ;

           ;

RUN;

[ballardw]

For your first request I don't think it is going to be practical unless you add an additional grouping variable.

Your current data has variables FYF14C and FYF14B which your request would result in both columns having FYF14.

For your second part move the ALL to before the Program. Also you can have a common statistic request for multiple variables by using parentheses

(All='Total' Program)*sum

to remove a row/column header often just using

VariableName=' ' works but you're adding so many style elements that force it to appear, if you don't want qualifier then Qualifier = ' ' and get rid of the style elements (if you don't want the text then the appearance modifiers are a waste)

HOWEVER if qualifier only has one value then drop it entirely and just use an ALL statement, it will total all rows of data if not crossed with anything.

If you want 3 datasets worth of data then combined the data sets before proc tabulate.

Data want;

      set data1 data2 data3;

run;

for example. If the 3 sets have the same characteristics then no problem. If your character variables have different lengths then you may have to specify the length of some variables BEFORE the set statement.

Getting the order may be a tad difficult sometimes. If the data can be sorted into the order you want then order=data for that variable may work. If your 3 data sets are the A B C elements then use the set statement above in the order you want and then then order=data on the class statement.

You might post some example data and a cleaner (i.e fewer columns) of desired output. Your data structure may need to be modified.

[jen123]

I have 2 outstanding items: 1. I would like to put heavy borders around the sections called Benefit and Cost....see attached file.  2. I have 3 datasets (aaa, bbb, ccc); I do not want to combine then into 1 dataset; For each dataset, I have a PROC TABULATE to create a report that results with one box/image; I would like to have the 3 images (of each dataset) on one page - stacked one above the other. **************************** PROC TABULATE DATA=aaa FORMAT=MILLION.; VAR YTD14 FYF14 FYF15 FYF16 FYF17 FYF18 FYF5Y ; CLASS InitiativeType /DESCENDING ORDER=UNFORMATTED MISSING; CLASS ForecastQualifier /ASCENDING ORDER=UNFORMATTED MISSING; CLASS Program / ASCENDING ORDER=UNFORMATTED MISSING; CLASS ProjectName /ASCENDING ORDER=UNFORMATTED MISSING; CLASS Version / ORDER=UNFORMATTED MISSING; CLASS Type; TABLE /* Define Row Dimension */   ForecastQualifier={LABEL="" STYLE(CLASSLEV)={BACKGROUND=#FFFFFF cellwidth=100}}*(   /***Add Total row above Initiative***/   ALL={LABEL="Total"}*{STYLE={FONT_WEIGHT=BOLD FONT_STYLE=ROMAN BACKGROUND=#FFFFC4}}*       Sum={LABEL=""}   InitiativeType={LABEL="" STYLE(CLASSLEV)={BACKGROUND=#FFFFFF}}*(     /***Add Sub-Total row above Program***/   ALL={LABEL="Sub-Total"}*{STYLE={FONT_WEIGHT=BOLD FONT_STYLE=ROMAN BACKGROUND=#D6D6D6}}*       Sum={LABEL=""}   Program={LABEL="" STYLE(CLASSLEV)={BACKGROUND=#FFFFFF}}*         Sum={LABEL=""}       )), /* Define Column Dimension */ Type=''*(YTD14 FYF15 FYF16 FYF17 FYF18 FYF5Y) /*Table Options */ / BOX={LABEL="PLAN" STYLE={FONT_WEIGHT=BOLD FONT_STYLE=ROMAN BACKGROUND=#FFFFFF JUST=LEFT}} MISSTEXT="" ; ; RUN;

[ballardw]

Without data I'm not going to try to answer as the data layout may affect the approach. Also I'm too lazy to reformat that much code to where I can read it easily. You may want to copy/paste code from SAS editor to NOTEPAD or similar plain text editor, then copy from there to paste here so we don't have to deal with word wrap and such.

[jen123]

Got it!  I didn't know why my codes came across as that.  I tried to different ways and obviously unsuccessful.  I didn't think of the NOTEPAD.  Please be patient with fairly new users. Thanks!

[ChrisHemedinger]

I copied the code into EG, hit Ctrl+I to reformat it, added a couple of line feeds in  TABLE statement, then right-click, Copy as HTML.  Then I pasted it here into the "HTML" view.  Voila!

PROC TABULATE DATA=aaa FORMAT=MILLION.;
    VAR YTD14 FYF14 FYF15 FYF16 FYF17 FYF18 FYF5Y;
    CLASS InitiativeType /DESCENDING ORDER=UNFORMATTED MISSING;
    CLASS ForecastQualifier /ASCENDING ORDER=UNFORMATTED MISSING;
    CLASS Program / ASCENDING ORDER=UNFORMATTED MISSING;
    CLASS ProjectName /ASCENDING ORDER=UNFORMATTED MISSING;
    CLASS Version / ORDER=UNFORMATTED MISSING;
    CLASS Type;

    TABLE /* Define Row Dimension */
        ForecastQualifier={LABEL="" STYLE(CLASSLEV)={BACKGROUND=#FFFFFF cellwidth=100}}*(  
            /***Add Total row above Initiative***/
            ALL={LABEL="Total"}*{STYLE={FONT_WEIGHT=BOLD FONT_STYLE=ROMAN BACKGROUND=#FFFFC4}}*      
            Sum={LABEL=""}   InitiativeType={LABEL="" STYLE(CLASSLEV)={BACKGROUND=#FFFFFF}}*(    
            /***Add Sub-Total row above Program***/
            ALL={LABEL="Sub-Total"}*{STYLE={FONT_WEIGHT=BOLD FONT_STYLE=ROMAN BACKGROUND=#D6D6D6}}*      
            Sum={LABEL=""}   Program={LABEL="" STYLE(CLASSLEV)={BACKGROUND=#FFFFFF}}*        
            Sum={LABEL=""}       )),
            /* Define Column Dimension */
        Type=''*(YTD14 FYF15 FYF16 FYF17 FYF18 FYF5Y)
            /*Table Options */
        / BOX={LABEL="PLAN" STYLE={FONT_WEIGHT=BOLD FONT_STYLE=ROMAN BACKGROUND=#FFFFFF JUST=LEFT}} MISSTEXT="";
        ;
RUN;