BookmarkSubscribeRSS Feed
juliajulia
Obsidian | Level 7

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

6 REPLIES 6
ballardw
Super User

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
Obsidian | Level 7

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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
Super User

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1445 views
  • 0 likes
  • 3 in conversation