Proc tabulate question

[megalops]

I am learning SAS and this is my first post on the forum. I hope I can crearly explain my problem.

I used Proc Tabulate to sum up the weight for a fish species. It worked great, but I need to also add an aggregate sum of two of the species. Here is the layout of the table that I already have:

species weight
X 25
Y 20
Z 34

I want to add an extra row. Here is a layout of the table that I want:

species weight
X 25
Y 20
Z 34
X+Y 45


The problem is that I don't know how to get proc tabulate to add the extra row of specis X and Y. I am assuming I need to add something to the tables statement of the procedure but I am not sure.

Any help would be greatly appreciated.

[data_null__]

You can do that with a multilabel format.

[pre]
data fish;
input species:$1. weight;
cards;
X 25
Y 20
Z 34
;;;;
run;
proc format;
value $sp(multilabel notsorted)
'X'='X'
'Y'='Y'
'Z'='Z'
'X','Y'='X+Y';
run;
proc tabulate;
class species / mlf order=data preloadfmt;
format species $sp.;
var weight;
tables species,weight*sum;
run;
[/pre]

[megalops]

I am sorry, I am struggling with the multilabel format.

Is there some text I can add to the tables statement telling sas to do an additional sum of only certain species?

[ballardw]

When formats are used on the CLASS variables the groupings are controlled by the format and no specification is needed in the table statement.
If you actually need to create more groups than your example then add them to the FORMAT used for the CLASS variable.

[data_null__]

The multilabel format is not the only option. You can create "new" species that are the combinations you want.

Give more info regarding your specific needs.

[Cynthia_sas]

Hi:
Multilabel formats were designed for exactly this purpose. For example, given your data, look what happens when a TOTAL and a percent are added:
[pre]
+-------------------------------------------------+
| | weight |
| +-------------------------+
| | Sum | PctSum |
+-----------------------+------------+------------+
|species | | |
+-----------------------+ | |
|X | 25.00| 31.65|
+-----------------------+------------+------------+
|Y | 20.00| 25.32|
+-----------------------+------------+------------+
|Z | 34.00| 43.04|
+-----------------------+------------+------------+
|X+Y | 45.00| 56.96|
+-----------------------+------------+------------+
|All | 79.00| 100.00|
+-------------------------------------------------+
[/pre]

The nice thing about using multi-lable formats is that X+Y can be taken into account without having an impact on the overall N or Count of 79.

Multi-label formats are not difficult -- it's a matter of knowing when to use them. My rule of thumb is whether I need "double-counting" -- where I want to count an observation in the category based on one class (such as the individual X and Y values), but then I also want to "double-count" the X+Y together--that's when to design a multi-label format. But designing the multilabel format is only half the battle, you design the format with PROC FORMAT, using the options (multilabel notsorted). Then you have to USE the format inside PROC TABULATE with the MLF and PRELOADFMT on your CLASS statement.

Many folks can work with TABULATE a long time and never run into the need for multilabel formats. There is nothing you can specify in the TABLE statement to sum X+Y on the report.

cynthia

[megalops]

I am stuck. I don't get any error messages yet SAS does not provide the aggreate sum of the three species.

First, I already read in a spreadsheet into SAS with the weight of different species. I called the data sarec. I have over 4,000 records with 10 different species.

I defined my species and my aggregate of three of the species with the following proc format code:

proc format data=sarec;
value $sp(multilabel notsorted)
'black grouper'='black grouper'
'red grouper'='red grouper'
'gag'='gag'
'black grouper','red grouper','gag'='Black, Red, and Gag grouper'
'golden tilefish'='golden tilefish'
'red snapper'='red snapper'
'snowy grouper'='snowy grouper'
'speckled hind'='speckled hind'
'vermillion snapper'='vermillion snapper'
'warsaw grouper'='warsaw grouper';
run;

Then I added the format code to my proc tabulate with the code below. new_com is the column in my dataset with the species names.

format new_com $sp.;

The proc tabulate provided the sum of weights for each species but did not provide a sum of weights of my three species aggregate (Black, Gag, and Red grouper).

Any advice would be greatly appreciated.

[Cynthia_sas]

Hi:
I can think of a few things:
1) did you modify the CLASS statement with MLF and PRELOADFMT???

2) the format will ONLY work for 'black grouper' for example -- it will not work for Black grouper or Black Grouper or black Grouper -- so whatever is in your data is how you should specify the species name in the format. If the data are not standard, you might want to explicitly uppercase all the species when you read the values and the use uppercase in the format.

3) to see the total N, as I showed, you would use the keyword ALL in your TABLE statement

cynthia

[megalops]

My bad. I do get an error message. The message says:

ERROR 22-322: Syntax error, expecting one of the following: ;, ALLDUPS, CNTLIN, CNTLOUT, DDNAME,FMTLIB, INDD, LIB, LIBRARY, MAXLABLEN, MAXSELEN, NOREPLACE, NOTEXT82, OUT, OUTDD,PAGE.

ERROR 76-322: Syntax error, statement will be ignored.

I believe the error is comming from the following line of code:

value $sp(multilabel notsorted)

I do not understand what $sp(multilabel notsorted) means. Is the (multilabel notsorted) a note that should be excluded from the code?

[Cynthia_sas]

PROC FORMAT does not take a DATA= option. If you compare your code to the PROC FORMAT code posted previously, you should see that there is no DATA= specified.

cynthia

[ballardw]

Also order of definition in the value section of proc format statements makes a difference in output.

Please see an example below.

/* a further example with multilabel formats */
/* the indents are just to show the desired levels */
/* I think that the order the statements occur in */
/* format may have an affect based on the options */
/* in Proc Tabulate. The spaces in the label are */
/* actually character 255 (Alt+255 on numeric pad) */
/* Motor and Water are in that order to show that */
/* the output order is sorted in the manner we want*/
/* not by the text of the responses */
/* (at least one case below ) */

proc format library=work;
value accidentl (multilabel notsorted)
1-5 = 'Accidents'
1-3 = ' Transport accidents'
1 = '   Motor vehicle accidents'
2 = '   Water, air, and space'
3 = '   Other land transport accidents'
4-5 = ' Nontransport accidents'
5 = '   Fishing'
;
value accidentr (multilabel notsorted)
1-5 = 'Accidents'
1-3 = ' Transport accidents'
4-5 = ' Nontransport accidents'
1 = '   Motor vehicle accidents'
2 = '   Water, air, and space'
3 = '   Other land transport accidents'
5 = '   Fishing'
;
value mf
1 = "Male"
2 = "Female"
;
run;

/* populate a dataset to display */
/* This specifically does NOT generate any data for FISHING above*/
/* to display the behavior of the options below in those cases. */
data junk;
do i=1 to 50;
type = round(4*ranuni(1234)+.5);
sex = round(2*ranuni(3455)+.5);
output;
end;
run;

/* Notice that before we get here the data is NOT sorted */
/* in any manner!!!! */

proc tabulate data=junk order=data;
class type / mlf PRELOADFMT;
class sex;
table type=' ', all='Total'*n=' '*f=comma9. sex=' '*n=' '*f=comma8.
/rts=35 printmiss misstext='0';
format type accidentl. sex mf.;
title 'Option Order=Data Preloadfmt Printmiss Misstext=0 format accidentl';
run;
proc tabulate data=junk order=data;
class type / mlf PRELOADFMT;
class sex;
table type=' ', all='Total'*n=' '*f=comma9. sex=' '*n=' '*f=comma8.
/rts=35 printmiss misstext='0';
format type accidentr. sex mf.;
title 'Option Order=Data Preloadfmt Printmiss Misstext=0 format accidentr';
run;
title;