topic Re: PROC TABULATE with percentage calculations in New SAS User

PROC TABULATE with percentage calculations

kenwill — Fri, 30 Nov 2018 19:31:08 GMT

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

Re: PROC TABULATE with percentage calculations

ballardw — Fri, 30 Nov 2018 19:43:29 GMT

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

Re: PROC TABULATE with percentage calculations

kenwill — Fri, 30 Nov 2018 21:04:21 GMT

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

Re: PROC TABULATE with percentage calculations

ballardw — Fri, 30 Nov 2018 22:17:56 GMT

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

Re: PROC TABULATE with percentage calculations

kenwill — Sat, 01 Dec 2018 21:48:45 GMT

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