Calculate Column Percentage sas

Question

I have the following dataset:

Date        Occupation      Tota_Employed
1/1/2005      Teacher         45
1/1/2005      Economist       76
1/1/2005      Artist          14
2/1/2005      Doctor          26
2/1/2005      Economist       14
2/1/2005      Mathematician   10

and so on until November 2014

What I am trying to do is to calculate a column of percentage of employed by occupation such that my data will look like this:

Date        Occupation      Tota_Employed   Percent_Emp_by_Occupation
1/1/2005      Teacher         45               33.33
1/1/2005      Economist       76               56.29
1/1/2005      Artist          14               10.37
2/1/2005      Doctor          26               52.00
2/1/2005      Economist       14               28.00
2/1/2005      Mathematician   10               20.00

where the percent_emp_by_occupation is calculated by dividing total_employed by each date (month&year) by total sum for each occupation to get the percentage:

Example for Teacher: (45/135)*100, where 135 is the sum of 45+76+14

I know I can get a table via proc tabulate, but was wondering if there is anyway of getting it through another procedure, specially since I wanted this as a separate dataset.

What is the best way to go about doing this? Thanks in advance.

Answer 1

Extract month and year from the date and create a key:

data ds;
set ds;
month=month(date);
year=year(date);
key=catx("_",month,year);
run;

Roll up the total at month level:

Proc sql;
create table month_total as
select key,sum(total_employed) as monthly_total
from ds
group by key;
quit;

Update the original data with the monthly total:

Proc sql;
create table ds as
select a.*,b.monthly_total
from ds as a left join month_total as b
on a.key=b.key;
quit;

This would lead to the following data set:

Date Occupation Tota_Employed monthly_total 1/1/2005 Teacher 45 135 1/1/2005 Economist 76 135 1/1/2005 Artist 14 135

Finally calculate the percentage as:

data ds;
set ds;
percentage=total_employed/monthly_total;
run;

Answer 2

Here you go:

proc sql;
create table occ2 as 
select
    occ.*,
    total_employed/employed_by_date as percentage_employed_by_date format=percent7.1
from 
    occ     a
    join
    (select 
        date,
        sum(total_employed) as employed_by_date
    from occ
    group by date)  b
        on
        a.date = b.date
;
quit;

Produces a table like so:

One last thought: you can create all of the totals you desire for this calculation in one pass of the data. I looked at a prior question you asked about this data and assumed that you used proc means to summarize your initial data by date and occupation. You can calculate the totals by date as well in the same procedure. I don't have your data, so I'll illustrate the concept with sashelp.class data set that comes with every SAS installation.

In this example, I want to get the total number of students by sex and age, but I also want to get the total students by sex because I will calculate the percentage of students by sex later. Here's how to summarize the data and get counts for 2 different levels of summary.

proc summary data=sashelp.class;
class sex age;
types sex sex*age;
var height;
output out=summary (drop=_freq_) n=count;
run;

The types statement identifies the levels of summary of my class variables. In this case, I want counts of just sex, as well as the counts of sex by age. Here's what the output looks like.

The _TYPE_ variable identifies the level of summary. The total count of sex is _TYPE_=2 while the count of sex by age is _TYPE_=3.

Then a simple SQL query to calculate the percentages within sex.

proc sql;
create table summary2 as
select
    a.sex,
    a.age,
    a.count,
    a.count/b.count as percent_of_sex format=percent7.1
from
    summary (where=(_type_=3))  a  /* sex * age  */
    join
    summary (where=(_type_=2))  b  /* sex  */
        on
        a.sex = b.sex
;
quit;

Answer 3

The answer is to look back at the questions you have asked in the last few days about this same data and study those answers. Your answer is there.

While you are reviewing those answers, take time to thank them and give someone a check for helping you out.