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
@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.
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.
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
@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.
It works! I will apply the same approach in future. Thanks very much!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.