Your SAS programs, embedded in web apps and elsewhere

Append Proc Tabulate

Reply
Super Contributor
Posts: 477

Append Proc Tabulate

Is there a way to append rows to a proc tabulate display in SAS 9.2, similar to using a union statement to combine two datasets?  I have a number of tabulate blocks that I like however I need to do some calculations that would either require a separate table below the tabulate display or reformatting to proc report.  I could make the separate table have the same spacing as the tabulate block by counting max character length in the tabulate function.

SAS Super FREQ
Posts: 8,739

Re: Append Proc Tabulate

Hi:

  I'm not entirely sure I understand what you want. However, generally, you cannot append or add rows to TABULATE output. A single TABULATE step can have multiple TABLE statements, but it sounds like you want 1 TABULATE, do some calculations and then have another separate TABULATE step. It really does sound more like PROC REPORT. But without seeing the data or understanding what you need, it's hard to do more than guess.

cynthia

Super Contributor
Posts: 477

Re: Append Proc Tabulate

I have three progressively more complicated goals.

Goal 1:

At the bottom of a tabulate block I want to display an additional row that I can create using calculations.   This might even be pushing the proc report calculate feature over the max because it appears that the proc report calculate function only does math within the currently referenced row.  The calculated Row should display the percent increase compared to the previous year.

  1. E.g. ((previous year – current year) /previous year) *100.

I can create this separately in SAS using arrays.  Or in SQL directly.

  1. E.g.

data transposed;

            set pre_transposed;

            array contrib{8} col1-col8;

            array diff{8};

            do i=1 to 8;

                        if i >= 2 then do;

                                    diff{i} = ((contrib{i-1} -contrib{i})/contrib{i-1});

                        end;

            end;

            drop i _Name_;

            keep _label_ diff1-diff8;

run;

Visual of goal one


Year1

Year2

Year3

Widget Type 1

2

1

45

Widget Type 2

3

8

3

Total

5

9

48

Percent Difference to previous year

.

-80

-433.33


Goal two (not sure if this is possible in SAS without complex manual SQL) is to display the calculated row each time I have a proc tabulate All statement, for the subtotal or total. 

Fall 2009

Fall 2010

Fall 2011

Fall 2012

Fall 2013

Fall 2014

Product Type1

Widget 1

48

3

4

5

6

7

Widget 2

1

3

4

5

6

7

Sub-total

49

6

8

10

12

14

Subtotal Percent Difference to previous year

.

87.75

-33.33

-25

-20

-16.66

Product Type 2

Widget 1

2

.

.

.

.

.

Widget 2

4

22

18

5

23

4

Sub-total

69

6

5

6

5

4

Sub-total

73

28

23

11

28

8

Subtotal Percent Difference to previous year

.

61.64384

17.85714

52.17391

-154.5471.42

Total

122

34

31

21

40

22

Subtotal Percent Difference to previous year

.

72.13

  1. 8.82
  1. 32.25
-90.4745

Goal three (highly wishful thinking)  is to display at least 3 levels of drilldown on both the side and top with the calculation included in the middle.


One option that I might use is to display a proc report row at the bottom of a tabulate block that has the exact same spacing as the tabulate block.  The trick is how to format it to space identically.

E.g.

proc report data=lastTwoRows noheader;

  /*spacing code goes here*/

  format diff1-diff8 4.2;

run;

Super Contributor
Posts: 477

Re: Append Proc Tabulate

Ballardw,

Does the second table option allow me to display the second table in a summary row fashion?  That solution might work, this sounds like the proc is going to be a little complicated. 

Super Contributor
Posts: 477

Re: Append Proc Tabulate

If there were a way to merge a summary row in with the tabulate detail row it might work. However the two datasets are on two different levels when displayed with tabulate.

Super User
Posts: 10,454

Re: Append Proc Tabulate

This is looking like you want to pre-summarize the data and then use either proc tabulate or report to display the results though it might be possible in Proc IML.

BTW I believe you may have the order of subtraction in your change percentage incorrect. If last years sales were 5 and this year 9 I would expect the change to be positive.

SAS Super FREQ
Posts: 8,739

Re: Append Proc Tabulate

Hi:

  It seems to me that Goal 1 is do-able with PROC REPORT, but to get the previous year values, they would either have to be macro variable values for the calculation or they would have to be NOPRINT items in the PROC REPORT table. PROC REPORT will allow you to write an extra summary line.
    

  Goal 2 you say is "is to display the calculated row each time I have a proc tabulate All statement, for the subtotal or total" I would say the only way to make this happen would be to write your original tabulate table to a dataset, do your calculation, add the row you want and then send the data to another procedure, like REPORT or PRINT. But, take a look at the output table created by TABULATE -- it does NOT retain the TABULATE structure -- it's more like a flattened view of the TABULATE info, with the _TYPE_ variable telling you which crossing of class variables the calculated statistics are for. So, this is probably not worth doing with TABULATE, I would think it would be more likely to accomplish with two passes through PROC REPORT.
     

Goal 3: drilldown on class variables is totally do-able with either REPORT or TABULATE -- you click on the CLASS variables cell (shown at the top and sides of your table sample). I don't know what you mean by "with the calculation included in the middle."
      

You really can't display a PROC REPORT "row" at the end of a TABULATE "block". The drilldown links are fairly easy -- you can have a user-defined format in either REPORT or TABULATE, but only REPORT gives you more dynamic URL building capabilities with the CALL DEFINE statement. The code below just illustrates making the links. They are not really meaningful links -- just going to Google. But you'll get the idea.

 

  The complicating factor in all this is that you've posted to the Stored Process forum and depending on your client application, the drill down URLs may or may not work. (For example, they might not work in Powerpoint or Web Report Studio.)

Cynthia

proc format;

  value $clink 'CANADA'='https://www.google.com/#q=CANADA'

               'GERMANY'='https://www.google.com/#q=GERMANY'

               'U.S.A.'='https://www.google.com/#q=U.S.A.'

               'Total' = 'http://www.sas.com';

run;

     

ods html file='c:\temp\prdsale_link.html';

proc report data=sashelp.prdsale;

  column country prodtype actual,quarter;

  define country / group

     style(column)=Header{url=$clink.};

  define prodtype / group style(column)=Header;

  define actual / ' ';

  define quarter / across;

  break after country / summarize style=Header;

  compute after country;

    line ' ';

  endcomp;

  rbreak after / summarize style=Header;

  compute after ;

    if _break_ = '_RBREAK_' then do;

    country = 'Total';

end;

  endcomp;

  compute prodtype;

    length lvar $75;

    lvar = catt('https://www.google.com/#q=',country,'+',prodtype);

    call define(_col_,'url',lvar);

  endcomp;

run;

ods html close;

Super User
Posts: 10,454

Re: Append Proc Tabulate

Tabulate does support out=<dataset> and you can send multiple tables, if needed, into that dataset (variable table tells which one contributes the records). I will not say it is the easiest set to work with but possible. It may be that you can work with this output but you would still need another proc to display the results.

Details?

Super User
Posts: 9,656

Re: Append Proc Tabulate

You can achieve it by making several new variables  or  if you can , just change the HTML resource code directly via sas data step.

Super Contributor
Posts: 477

Re: Append Proc Tabulate

Visually this is what the no print line would need to look like.

Year1Year2Year3
Total line5948
Previous year NOPRINT line59
Calculated line80433.3333

=((current year widgets - previous year widgets)/year current widgets)*100

In order to create the NOPRINT line I need to be able to reference a cell of a previous row researching how to do this.

Super User
Posts: 9,656

Re: Append Proc Tabulate

Hard to catch on you . It would better post some sample data , and the output you need (better it is a picture).

Cynthia and others could offer you some good suggestion.

Super User
Posts: 6,926

Re: Append Proc Tabulate

When everything else fails, consider doing it in a data _null_ step and write the report "manually" to the html file. Means a lot of writing, but you gain total control over the output.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 477

Re: Append Proc Tabulate

I made the line manually for goal one and decided it was too much work for the other goals. 

  1. E.g.

proc report data=lastTwoRows  noheader;

     col headerCol diff1-diff8;

     format diff1-diff8 4.2;

     define headerCol / style(column)=[cellwidth=2in];

     define diff1-diff8 / style(column)=[cellwidth=.75in];

run;

Ask a Question
Discussion stats
  • 12 replies
  • 1415 views
  • 6 likes
  • 5 in conversation