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

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

fieldsa83 — Mon, 30 Jan 2017 15:44:11 GMT

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"?

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

ballardw — Mon, 30 Jan 2017 15:47:46 GMT

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

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

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

fieldsa83 — Mon, 30 Jan 2017 15:52:52 GMT

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;

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

ballardw — Mon, 30 Jan 2017 16:58:33 GMT

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.