Create table of multiple variables

[kenwill]

Hi,

I am new to SAS and would need some expert advice.

I try to create a table of 3 variables. The hypothetical data is saved as table.sas7bdat as attached.

Also attached is the report table format I need (see Table.xlsx).

I used the follow proc freq codes to create the table I want but it gives me 3 separate tables instead. Also, I couldn't produce the column formats I need. 

proc freq data = test.table (where=(temperature not in ('warm')));
table month*time*temperature / out = class norow nocol nopercent
;
run
;

Please advise me what's the correct codes to produce the table. 

Also, is it possible to use PROC TABULATE or some other approach to create the same table?

Thank you!

Regards, 

Ken

[ballardw]

See if this gets you started;

proc tabulate data=test.table;
class month time temperature;
table time=' ' all='Total',
      month=' '*temperature=' '*n=' '
      ;
run;

The =' ' after the variables and the n statistic (to count) are to suppress variable name or labels.

you can add a WHERE statement to filter data or a where data set option.

[kenwill]

Hi,

Thanks so much! This is very helpful. 

I have added a 'where' statement to filter out the 'warm' category. 

proc tabulate data=test.table;
where temperature ~= 'warm';
class month time temperature;
table time=' ' all='Total',
month=' '*temperature=' '*n=' '
;
run;

However, I want just 2 columns for each month: first column for the combined frequency of cool, cold, and freeze (and name the variable as 'not warm'), and the second column for just the freeze frequency. Any advice on how should I approach it? Appreciate.

Thank you.

Regards, 

Ken

[ballardw · Accepted solution]

---

@kenwill wrote:

Hi,

Thanks so much! This is very helpful. 

I have added a 'where' statement to filter out the 'warm' category. 

proc tabulate data=test.table;
where temperature ~= 'warm';
class month time temperature;
table time=' ' all='Total',
month=' '*temperature=' '*n=' '
;
run;

However, I want just 2 columns for each month: first column for the combined frequency of cool, cold, and freeze (and name the variable as 'not warm'), and the second column for just the freeze frequency. Any advice on how should I approach it? Appreciate.

Thank you.

Regards, 

Ken

---

There are two basic approaches to treating multiple values as a single category for reporting. One is to go back to your data and create an additional variable with if/then/else statements. Which can get tedious repeated visits to keep adding different variables.

For values based on a single variable a custom format is often preferable as you need not add any variables and is often easier to write ranges of values for numeric variables. Also a format may be applicable to multiple variables.

Tabulate is one of the few procedures that uses multilabel formats to do such things as group and individual values so that may be the way to go:

proc format library=work;
value $coldtemp (multilabel)
'cool','cold','freeze'= 'not warm'
'freeze' = 'freeze'
;
run;

proc tabulate data=test.table;
   class month time;
   class temperature / mlf;
   format temperature $coldtemp.;
   table time=' ' all='Total',
         month=' '*temperature=' '*n=' '
         ;
run;

However sort order of the formatted values can depend on the order a format is written and the sub groups.

[kenwill]

It works! I will apply the same approach in future. Thanks very much!!