DATA Step, Macro, Functions and more

Proc Tabulate Custom Percent Calculation

Reply
Occasional Contributor
Posts: 14

Proc Tabulate Custom Percent Calculation

Hello

 

I am trying to figure out how to do custom percentage using Proc Tabulate. I have given the table below as example for only one race. I need to do this for all races. The actual data looks like this;

 

IDGenderRacegrad_status
1FemaleNRALienTransfer without Graduation
2FemaleNRALienTransfer without Graduation
3FemaleNRALien 
4FemaleNRALien 
5FemaleNRALienTransfer without Graduation
6FemaleNRALienTransfer without Graduation
7FemaleNRALienTransfer without Graduation
8MaleNRALien 
9MaleNRALien 
10MaleNRALien 
11MaleNRALien 
12MaleNRALien 

 

I did a Proc Tabulate on numbers and it looks like below. The table shows for all races. 

 

45.JPG


I will use Non_resident Alien as example. The Proc Tabulate table above shows 5 female non-resident aliens who transferred. I want to know "Of the Total Female Non Resident Aliens, what percentage transferred"?. The first table shows a total of 7 Female Non Resident aliens and of them 5 transferred which makes it 71% for that cell (5/7=71%). 

 

Another example -there are 14 male Hispanic who graduated. I want to know - "Of all Male Hispanic Students", 14 graduated. According to my data (have not shown here), there are total of 66 Male Hispanic which makes that cell 14/66=21%

 

Basically I have to do this same percent calculation for all cells. 

 

Any help will be greatly appreciated.

Thanks

Super User
Posts: 13,941

Re: Proc Tabulate Custom Percent Calculation

Given  your data example you may want to presummarize counts and calculate the rate. My reasons for this are 1) your variable grad_status is apparently character which means it can only be used as a class variable, 2) unless you use the option missing for the class variable grad_status you will have a lot of records excluded (tabulate by default excludes any record with any class variable missing) and 3) the pctn<> calculation will create columns for the "missing" grad_status.

 

Your explanation is also a bit incomplete. You claim "The first table shows a total of 7 Female Non Resident aliens and of them 5 transferred which makes it 71% for that cell (5/7=71%).". I do not see any indication of 7 Female Non-resident aliens.

 

Sometimes it makes a lot of sense to create indicator variables such a 1/0 for "Transfer without Graduation". Create a variable if the status is know with 1 for true(Transfer without Graduation) and 0 for not. Then you can use tabulate statistics to do 3 things: n => how many valid records, sum => number of true(or yes or however you think of it) and mean will give percentage as a decimal.

 

You might get a more targeted response providing a small data set in the form a data step to test data with. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Ask a Question
Discussion stats
  • 1 reply
  • 60 views
  • 0 likes
  • 2 in conversation