Append Proc Tabulate

[DavidPhillips2]

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.

[Cynthia_sas]

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

[DavidPhillips2]

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.54	71.42
Total		122	34	31	21	40	22
Subtotal Percent Difference to previous year		.	72.13	8.82	32.25	-90.47	45

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;

[DavidPhillips2]

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. 

[DavidPhillips2]

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.

[ballardw]

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.

[Cynthia_sas]

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;

[ballardw]

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?

[Ksharp]

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

[DavidPhillips2]

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

	Year1	Year2	Year3
Total line	5	9	48
Previous year NOPRINT line		5	9
Calculated line		80	433.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.

[Ksharp]

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.

[Kurt_Bremser]

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.

[DavidPhillips2]

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;