Desktop productivity for business analysts and programmers

Summary Tables in EG

Reply
Frequent Contributor
Posts: 94

Summary Tables in EG

Hello,

I spent the past couple of days trying to build a report using Summary Tables (under Describe).  I've made some headways in my progress, but spent more hours than I think it should take me on the following items.  I would like to apologize if I exceeded the number of bullets allowed.  I am looking for assistance on how to complete this via EG.  The SAS-generated code is below in red.  I am currently using SAS EG 6.1.

  1. How do I show a blank cell where data is missing?  Today, I get a "." in the cell.
  2. How can I get the results all in one-page (fit to page) to pdf?  I added the options papersize, but that didn't work
  3. I have a Variable called FYF5Y.  However it does not show on the generated report.  This is missing in the Column Dimension below and I cannot figure out how to include it.
  4. I would like to get a Subtotal (by Initiative Type) and Total (by Forecast Qualifier).  I dragged the TOTAL (ALL) from the Class box and here is my result.  I changed the amount to $1.  I wasn't able to copy over the top rows.  Each of the gray column is a fiscal year.  Modifying this bullet.  After I submitted this posting, I figured out how to do this - thanks to a posting I found with Cynthia's response.  :smileygrin: 
  5. I would like to add a column at the end that is a subtraction of the first gray column from the last gray column.  I read on this community that this is not possible.  The thread was from 2012 so I am not sure if this is possible in the 6.1 EG version.  Also, under the Summary Tables task, I do not see a SUM in the available statistics to choose from - should I want to add columns.
  6. Is there a way to filter what I would like to show in the table?  Right now I am creating new dataset to include only what I want and build summary tables from that.  I don't believe this is the correct and efficient way to do this.  For example, can I filter out to show only Forecast (under Version)?

Thank you!

Version

ForecastQualifier

.

.

.

.

.

.

Forecast

2015

Total (ALL)

Program

A

.

.

.

.

.

.

B

C

.

.

.

.

.

.

D

.

.

.

.

.

.

E

.

.

.

.

.

.

F

.

.

.

.

.

.

G

.

.

.

.

.

.

H

.

.

.

.

.

.

I

.

.

.

.

.

.

2014

Total (ALL)

A

$1

$1

$1

$1

$1

.

B

$1

$1

$1

$1

$1

.

C

$1

$1

$1

$1

$1

.

D

$1

$1

$1

$1

$1

.

E

$1

$1

$1

$1

$1

.

F

$1

$1

$1

$1

$1

.

G

$1

$1

$1

$1

$1

.

H

$1

$1

$1

$1

$1

.

I

$1

$1

$1

$1

$1

.

Here is the SAS-generated code:

/* Start of custom user code. */

options orientation=landscape;

options papersize=(11in 8.5in);

/* End of custom user code. */

%_eg_conditional_dropds(WORK.STABSummaryTablesCOST_BENEFIT);

/* -------------------------------------------------------------------

   Run the tabulate procedure

   ------------------------------------------------------------------- */

PROC TABULATE

DATA=JEN1.COST_BENEFIT(FIRSTOBS=1 )

  OUT=WORK.STABSummaryTablesCOST_BENEFIT(LABEL="Summary Tables  for JEN1.COST_BENEFIT")

  FORMAT=MILLION.

  ;

  VAR FYF14 FYF15 FYF16 FYF17 FYF18 FYF19 FYF5Y;

  CLASS Version / ORDER=UNFORMATTED;

  CLASS ForecastQualifier / ORDER=UNFORMATTED DESCENDING;

  CLASS InitiativeType / ORDER=UNFORMATTED;

  CLASS Type / ORDER=UNFORMATTED;

  CLASS Program / ORDER=UNFORMATTED DESCENDING;

  TABLE /* Row Dimension */

Version*

  ForecastQualifier*

    Program*

      Sum={LABEL=""},

/* Column Dimension */

FYF14

FYF15

FYF16

FYF17

FYF18

FYF19

/*Table Options */

/ MISSTEXT="" ;

  ;

RUN;

Super User
Posts: 11,134

Re: Summary Tables in EG

Partial response:

1) The "." you are getting is a system option for missing data. You should be able to change that to any single character but not using EG I'm not sure what the point and grunt approach would be.

2) The size of PDF might be able to fit by using a different style that uses a smaller font such a Meadow or Seaside. OR spend a lot of time with style overrides.

3) Note that in the column dimension FYF5Y does not appear. You may have missed a selection.

5) I don't know which report generator you called but one that will call Proc Tabulate cannot do the calculation requested. Proc Report can but that will require going through another set of selections.

6) Normally a Where statement can be used with any procedure such as: where Version='Forecast' or similar as a dataset option.

Ask a Question
Discussion stats
  • 1 reply
  • 261 views
  • 0 likes
  • 2 in conversation