Report with Totals and Percent of total at bottom with Proc Tabulate (or other procedure)

[BBSHOPSINGER]

I'm trying to get away from using DDE or other methods to put data into Excel, and use reporting procedures such as Proc Tabulate.  My issue is I want to create a simple report that looks something like the attached file:

I can get everything but the bottom row with proc tabulate easily, but I can't seem to find any way to put percent of total in.  Everything I try either produces percentages in a column, or errors.  Is this kind of report do-able in SAS (SAS 9.4)?  Thanks for any help you might be able to give.  (Long time programmer, but not much experience with reporting procedures). 

[ballardw]

It would help to show your proc tabulate code.

Generally the ALL predicate is used in a table definition to do cumulative statistics but where to place it will depend on your current syntax, if it will be at all possible.

An example data set to work with would be helpful. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

[BBSHOPSINGER]

I was only trying to show an example of what the report should look like.  I tried pasting, and it pasted as HTML successfully, but was not allowed to post.  I'll work on creating a text version of what I'm looking for.  Since I can't share my actual data or code, sample code would look something like this:

data sample;
	format vintage $4. type $6. amount dollar5.;
	input vintage $ type $ amount;
	cards;
2014 Type_A 20
2014 Type_B 10
2014 Type_C 30
2015 Type_A 10
2015 Type_B 30
2015 Type_C 30
2016 Type_A 30
2016 Type_B 10
2016 Type_C 30
2017 Type_A 20
2017 Type_B 10
2017 Type_C 30
;
run;
proc tabulate data=sample;
	var amount;
	class vintage type;
	table /* ROW Statement */
		vintage =" " all="Total",
			/* COLUMN Statement */
		type =' '*amount=" "*sum=" "*f=dollar5. all="Total"* amount=" "*
			sum=" "*f=dollar5./box="Vintage";
run;	

The resulting report is a very simple summary with totals.  What I would like to add is another row at the bottom with percentages of the total column as a percent of the sum total of all.  All of the examples I find are adding additional columns for percentages, which wouldn't work for this report.

It's an extremely easy thing to export or use ods to get the data out into Excel and then add the percentage afterward, but I like to keep everything as automated as possible.  I would like to think that if it's easy to do in Excel, I should be able to do it in SAS.  Any ideas? 

[ballardw · Accepted solution]

One way to get a percent for the totals only:

proc tabulate data=sample;
	var amount;
	class vintage type;
	table /* ROW Statement */
		vintage =" " * amount=" "*sum=" "*f=dollar5.
      all=" "*amount=" "*(sum=" Total"*f=dollar5.
                              pctsum="Percent of total")
              ,
			/* COLUMN Statement */
		type =' 'all="Total"
     /box="Vintage" row=float;
run;	

Since the PCTSUM statistic does the multiply by 100 you wouldn't want to use a percent8. type format as then you would see things like 3077% instead of 30.77.

I will leave to the interested reader the games to play with mult in a custom picture format to display 30.7$ from pctsum.

[BBSHOPSINGER]

Thanks!  Every time I tried to do something similar, I got the "Statistic other than N was requested without analysis variable in the following nesting" error.  Does the "row=float" option take that issue away?  Thanks again.

[ballardw]

---

@BBSHOPSINGER wrote:

Thanks!  Every time I tried to do something similar, I got the "Statistic other than N was requested without analysis variable in the following nesting" error.  Does the "row=float" option take that issue away?  Thanks again.

---

The ROW=Float will, depending on ODS destination, clean up the columns for the variable(s) whose labels were set to blanks. Otherwise you would get a blank column for the TOTAL and the AMOUNT variable.

The nesting error means that you asked for something like SUM in such a way that the VAR variable wasn't determinable.

Note that I moved the summaries for the years out of the column to the row. Likely your approach was leaving something in the column that wanted a summary that didn't mesh with the rows. I would have to see the specific code to identify which crossing was involved. I use tabulate a lot and occasionally spend some time chasing down these errors when I make a "minor" change that often ends up with a lot of code moving in the end.

[BBSHOPSINGER]

Thanks so much for your help!  I've used SAS for many years, but feel like a total neophyte when it comes to reporting procedures.  The availability of ODS Excel has made using these procedures much more interesting, as I can now get the information out in the format requested without going through the extreme data manipulation that I've done in the past.

Never too old to learn new stuff!

[Cynthia_sas]

Hi:
Row=Float removes empty cells from the row header area when you blank them out, as is done in this program (all the =" " in the row dimension.

The reason you get the Statistic other than N was requested is that you can only get N with ALL. Note how in the above TABLE statement the crossing is all*amount*(sum pctsum) (without the labels and formats). If you just had all*(sum pctsum), then you would get that error message because ALL when used with a statistic other than N needs to have an analysis variable somewhere in the crossing.

To format the percent without multiplying a second time by 100, you can use a custom PICTURE format for PCTSUM, as shown below.

proc format;
  picture pctf (round) low-high='009%';
run;
 
** and then in the TABLE statement;


proc tabulate data=sample;
	var amount;
	class vintage type;
	table /* ROW Dimension */
		vintage =" " * amount=" "*sum=" "*f=dollar5.
        all=" "*amount=" "*(sum=" Total"*f=dollar5.
                            pctsum="Percent of total"*f=pctf.),
			/* COLUMN Dimension */
		type =' ' all="Total"
     /box="Vintage" row=float;
  run;	

Note how the pctsum is now using a format of f=pctf.

cynthia

[BBSHOPSINGER]

Thanks for the information and tip! I'll add the picture format to my code as well.