topic Re: PROC Tabulate - Move row total, add column header, etc in SAS Enterprise Guide

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

jen123 — Tue, 12 Aug 2014 21:16:15 GMT

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;

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

ballardw — Tue, 12 Aug 2014 22:31:32 GMT

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.

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

jen123 — Wed, 13 Aug 2014 14:36:16 GMT

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;

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

ballardw — Wed, 13 Aug 2014 15:05:20 GMT

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.

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

jen123 — Wed, 13 Aug 2014 20:20:38 GMT

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!

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

ChrisHemedinger — Wed, 13 Aug 2014 20:42:46 GMT

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;