PROC TABULATE makes "all" row missing (with a '.') -- way to add label saying "TOTAL"?

Reply
Frequent Contributor
Posts: 75

PROC TABULATE makes "all" row missing (with a '.') -- way to add label saying "TOTAL"?

if you do

 

proc tabulate [...]

 

table (all sex)*date*weight*(sum)

 

[...]

 

It will make the "all" in sex have just a period '.'.

 

How can I make it say "Total"?

 

 

Grand Advisor
Posts: 10,239

Re: PROC TABULATE makes "all" row missing (with a '.') -- way to add label saying "TO

You can assign labels to variables or statatistics in Tabulate with ='Label text' after the item.

 

table (all='Total' sex)*date*weight ....

 

 

Frequent Contributor
Posts: 75

Re: PROC TABULATE makes "all" row missing (with a '.') -- way to add label saying "TO

Sorry, I should clarify:

 

In the actual "results" tab it will show the label "Canada" and "Total industry". 
 
However, in the output.test file it only shows these as blanks/missing with a period '.'
 
How can I get them to actually have the labels of "All" in the dataset?
 
 
 
proc tabulate data=output.combined OUT=OUTPUT.test;
 
%let weight=finalwt;
%let date=sdate;
 
class &date industry naics4 prov;
var &weight;
 
table 
(all='Canada' prov)*(industry='')*(all='Total industry' naics4=''), &date=''*&weight=''*(sum=''*f=comma10.0) ;
 
run;
Grand Advisor
Posts: 10,239

Re: PROC TABULATE makes "all" row missing (with a '.') -- way to add label saying "TO

You have a couple options but if your variable is numeric th only one you have is a custom format. You could use a data step and make sure that the length of character variables are set long enough to add text as needed with the values of the _type_ variable.

 

data want;

    set output.test;

    /* this is a guess based on your code you need to inspect the actual table to get the

    if _type_ in ('000','010') then prov='Canada'; 

run;

 

might work but if you have a numeric then you cannot place character values into a numeric column.

A custom format such as

proc format library=work;
   value $Prov
   ' '='Canada'
   ;
run;

would display Canada for the missing values of prov and the original values if prov is character. If you have an existing numeric format for categories then adding a category for .='Canada' or .='Total Industry' for your naics4 variable would work.

 

Note that this approach will not work if you use MISSING on your class variables as you have two types of missing possible and you would have to move into the realm of special missing and formats to get desired output.

Ask a Question
Discussion stats
  • 3 replies
  • 140 views
  • 0 likes
  • 2 in conversation