PROC TABULATE with percentage calculations

[kenwill]

Hi,

This is a follow up to a question I posted earlier. This time, I tried to do percentage calculations but not quite successful.

Please see attachments:

Data set: table.sas7bdat

2 tables I tried to create: Table2.xlsx

Below are my SAS codes. I can calculate the column percentages correctly, but not the row percentages.

Besides, I want to show the N and % side by side instead of two separate tables (one for N, another for %) like what I did here. How can I do that?

/* Column percentages */
proc tabulate data=test.table;
where temperature ~= 'warm' and month ~= 'december';
class month time;
class temperature / mlf;
table time=' ' all='Total',
month=' '*(temperature=' ' all)*n=' '
month=' '*(temperature=' ' all)*colpctn=' '
;
run;

/* Row percentages */

proc tabulate data=test.table;
where temperature ~= 'warm' and month ~= 'december';
class month time;
class temperature / mlf;
table time=' ' all='Total',
month=' '*(temperature=' ' all)*n=' '
month=' '*(temperature=' ' all)*rowpctn=' '
;
run;

Thank you for any advice!

Ken

[ballardw]

---

@kenwill wrote:

Hi,

This is a follow up to a question I posted earlier. This time, I tried to do percentage calculations but not quite successful.

Please see attachments:

Data set: table.sas7bdat

2 tables I tried to create: Table2.xlsx

Below are my SAS codes. I can calculate the column percentages correctly, but not the row percentages.

Besides, I want to show the N and % side by side instead of two separate tables (one for N, another for %) like what I did here. How can I do that?

---

You will have to describe what a "correct" row percentage may be.

The general approach in getting two or more statistics to appear together are parentheses.

Instead of

month=' '*(temperature=' ' all)*n=' '
month=' '*(temperature=' ' all)*colpctn=' '

try

month=' '*(temperature=' ' all)* (n=' ' colpctn=' ')

Though specific orders may depend on the nesting of different variables.

It is best to provide data in the form of  data step code. Then people using different operating systems and SAS versions can create data to test code with. You may not know that there are lots of people using different versions of SAS and some of them, even with the same operating system, may not be able to use your SAS data set because they have an older version.

[kenwill]

Hi,

Thanks for the advice!

I have attached the dataset in Excel format (Book1.xlsx). Would that help?

Regarding the row percentage calculations. How can I calculate them with respect to the subtotal (ie., per each month) instead of the whole row (pls see Table2.xlsx)? I am not familiar with nesting, any tips are most welcome.

Thanks!

Ken

[ballardw]

See if this example using the SASHELP.CARS data helps.

Note that the second table uses pctn<cylinders> to get the percentage within the values of cylinder for a single type of car.

proc tabulate data=sashelp.cars;
   where cylinders in (4 6 8);
   class make type cylinders;
   table make ,
         type*cylinders*(n rowpctn)
   ;
   table make ,
         type*cylinders*(n pctn<cylinders>)
   ;
run;

Do not use colpctn<> rowpctn<> colpctsum<> or rowpctsum<>. Those options are (or at least in previous versions of SAS) are so far from the expected submitted code that they will crash SAS. PCTN<> or Pctsum<> with a single variable. The <> says which variable to use for denominator. The variable must appear in the table request and things can get picky about order and dimension

Quite often it is more reliable to use Proc summary to summarize the data and then a data step to add percentages. And then use a report procedure like the Proc Print, Report or Tabulate (carefully) to show the summarized data

[kenwill]

Thank you. I will try your approach to see if it will work for my case.