how to use proc tabulate to get the result i want

[juliajulia]

hello,

i want to use proc tabulate to get the following table but don't know how to do it.

State	Male	Male_pct	Female	Female_pct	Transgender	Transgender_pct	total
AL
AK
AZ
AR

in my dataset, i have state, gender and other variables. the gender variable has the value of 1 (male), 2 (female), 3,4 (transgender).

also, can proc sql do this job as well.

Thank you very much

[ballardw]

I hope that you want a report and not a data set as the data sets proc tabulate creates are tad awkward to use until fairly experienced with SAS.

The generic approach would look like this (using AGE instead of State as you haven't provided any example data)

proc tabulate data=sashelp.class;
   class age sex;
   table age,
         sex*(n pctn) All='Total'*n
   ;
run;

Since you have two values that you want to display as "transgender" then you would create a custom format such as

proc format library=work;
value gender
1='Male'
2='Female'
3,4 = 'Transgender'
;
run;

and apply that format to the variable with a statement in proc tabulate like

format gender gender.;

You can change the label for a statistic with KEYWORD option if you want to apply it every where.

Or you can modify in the table syntax such as this to modify labels:

proc tabulate data=sashelp.class;
   class age sex;
   table age,
         sex=' '*(n pctn='%') all='Total'*n=' '
   ;
run;

By default any record that has missing values for any variable that appears on a class statement will be excluded by Proc Tabulate.

If you don't want the nested column appearance then you may want to summarize the data and do some manipulation to actually create "male_percent" type variables and display with proc print.

[juliajulia]

Thank you very much.

the code work well but the percentage i want is not the column percent but the raw percent. here is one of the example of how the percent calculated. 

State	Male	Male_pct	Female	Female_pct	Transgender	Transgender_pct	total
AL	100	50%	20	10%	10	5%	200
AK
AZ
AR

[juliajulia]

i modified the code a little bit and got the exact result i want. thank you very much.
proc tabulate data=rsr.clientinfo2018;
class state gender;
table state,
gender=' '*(f=8.0*n f=8.1*rowpctn="%" ) all='Total'*n=' '
;
run;

[ballardw]

You do have to be somewhat specific with Proc Tabulate and Percentages as you have table, row, column and page options in addition to specifying pctn<variablename> to use a count of another variable as the denominator.

Friendly warning if  experimenting with the pctn<variable> or pctsum<othervar> syntax: save your work before executing the proc tabulate as you can create conditions that crash SAS when you make a serious syntax mistake. I.e. do not attempt rowpctn<var>, colpctn<var>.

[heatherjae15]

Measure	Subject (N=000)
Age (yrs)	00 ± 00 (00) 00 (00, 00) [00,00]
Gender
Male	00% (00/00)
Female	00% (00/00)
Race
American Indian / Alaska Native	00% (00/00)
Asian	00% (00/00)
Black / African American	00% (00/00)
Native Hawaiian / Pacific Islander	00% (00/00)
Caucasian	00% (00/00)
Other	00% (00/00)
Ethnicity
Hispanic or Latino	00% (00/00)
Non-Hispanic or Latino	00% (00/00)
Height (cm)	00 ± 00 (00) 00 (00, 00) [00,00]
Weight (kg)	00 ± 00 (00) 00 (00, 00) [00,00]
BMI	00 ± 00 (00) 00 (00, 00) [00,00]

I want to make this table using the variables on the left and calculate means, standard deviation, median, min, max for continuous measures and percent and n/N for categorical variables.  Can you please help me with this?

[ballardw]

You variables that want to calculate a mean, stddev, min, max etc. Go on a VAR statement. To get the statistics within the value of a CLASS variable you nest the variable along with statistics.

Proc tabulate will support multiple table requests and since there are restrictions about how columns must contain the same statistic you may be better off with two tables from proc tabulate for your different requirements. What ever you do you are not going to get all of the values into a single cell. You can play games with some of the features to place values near each over but not multiple values in a single cell.

Here is an example that uses a data set you should have to create 3 tables with one call.

proc tabulate data=sashelp.class;
   class sex age;
   var height weight;
   table sex age ,
        (height weight) *(min mean median max std )
        / misstext=' '   ;

  table sex age    ,
        n colpctn
  ;
  table height weight,
        min mean median max std lclm uclm
  ;
        
run;