Proc Tabulate Custom Percent Calculation

anandas — Fri, 09 Feb 2018 15:18:25 GMT

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;

ID	Gender	Race	grad_status
1	Female	NRALien	Transfer without Graduation
2	Female	NRALien	Transfer without Graduation
3	Female	NRALien
4	Female	NRALien
5	Female	NRALien	Transfer without Graduation
6	Female	NRALien	Transfer without Graduation
7	Female	NRALien	Transfer without Graduation
8	Male	NRALien
9	Male	NRALien
10	Male	NRALien
11	Male	NRALien
12	Male	NRALien

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

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

Re: Proc Tabulate Custom Percent Calculation

ballardw — Fri, 09 Feb 2018 15:36:00 GMT

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-data-AKA-generate/ta-p/258712 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.

topic Re: Proc Tabulate Custom Percent Calculation in SAS Programming

Proc Tabulate Custom Percent Calculation

Re: Proc Tabulate Custom Percent Calculation