topic proc tabulate percentage and "where" statement in SAS Programming

proc tabulate percentage and "where" statement

dustychair — Thu, 09 Jun 2022 03:29:16 GMT

Hi all,

I have some questions on proc tabulate.

1. I want to get one decimal for percentages.

2. I want to put % sign after percentages.

3. EnglishLearnerEL variable is either Y or N. I want to display only when EnglishLearnerEL is Y. When I use where= EnglishLearnerEL="Y" it excludes EnglishLearnerEL =N and display only "Y" results. However, I want to show only EnglishLearnerEL =Y, not exclude them.

My code is below.

Thank you for your help

proc tabulate data=inc_sdf_alg_1 out=inc_sdf_alg_1_gender;
class  gender EnglishLearnerEL ;
table (gender EnglishLearnerEL)*(N*format=comma16. colpctn*f=percentn7.2) (all= 'Total'* format=comma16.),
 (all= 'Total'*format=comma16.);
 where EnglishLearnerEL="Y" ;
run;

Re: proc tabulate percentage and "where" statement

Kurt_Bremser — Thu, 09 Jun 2022 08:39:08 GMT

The WHERE statement filters the input, so PROC TABULATE never "sees" anything but the "Y" values, which then constitute 100%.

I suggest that you use PROC TABULATE with the OUT= option to create a dataset, and then print your report from that, filtering there without affecting the values.

For code suggestions, supply example data in a working data step with datalines, and show the expected result from that.

Re: proc tabulate percentage and "where" statement

Astounding — Thu, 09 Jun 2022 10:09:26 GMT

Here's an approach you can use for a Y/N variable when you want to print the percentage of Y values but not the percentage of N values.

In a DATA step, create a new variable that is 1 when the Y/N variable is Y, and 0 when it is N.

In PROC TABULATE, use that new variable in a VAR statement. Tell PROC TABULATE to compute the mean of that new variable, and print the mean in the percent8.1 format. Note that the mean of a 0/1 variable is the percentage of 1's.

Re: proc tabulate percentage and "where" statement

dustychair — Sat, 11 Jun 2022 04:24:24 GMT

Hi,

Thanks for suggestions. The first two statements resolved my first and second questions. Even though I could not resolve it this link is for the question 3.

134-2013: Tips for Generating Percentages Using the SAS® TABULATE Procedure

proc format;
picture pfmt (round) low-high='009.9%';

proc tabulate data=sdf.inc_sdf_&d._&j.  out=inc_sdf_&j._&d._desc;
class  gender EnglishLearnerEL ;
table Egender EnglishLearnerEL, (N*format=comma16.) (colpctn*f=pfmt.);
run;