topic Re: Create table of multiple variables in New SAS User

Create table of multiple variables

kenwill — Wed, 28 Nov 2018 18:43:23 GMT

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

Re: Create table of multiple variables

ballardw — Wed, 28 Nov 2018 20:42:03 GMT

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.

Re: Create table of multiple variables

kenwill — Wed, 28 Nov 2018 21:53:25 GMT

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

Re: Create table of multiple variables

ballardw — Wed, 28 Nov 2018 23:51:06 GMT

@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.

Re: Create table of multiple variables

kenwill — Thu, 29 Nov 2018 00:35:13 GMT

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