How to create a table of nested values and percentages

[shailey]

Hi, I hope I am submitting this correctly and I have tried to format it to be clear and concise. I am trying to create a table using proc tabulate that will contain the counts and percentages of (for example) job applicants, interviewees, and hires. My knowledge of SAS, on a scale of 1 = newbie to 10 = expert, probably about a 3 or 4. I use Base SAS and although I am very open to learning about new things (e.g. sql, proc report, etc), I really would prefer to solve this problem using only Base SAS if possible. I'm totally open to data restructuring ideas.

Say I have the following data:

data job_apps;

input year applied interviewed hired sex $ race $ fem_count min_count;

datalines;

2012      1      1      1      M     maj     .     .

2013      1      1      1      F      maj     1     .

2014      1      1      1      M     min      .     1

2012      1      1      1      F      maj     1     .

2013      1      1      .      M      maj     .     .

2014      1      1      .      F      min     1     1

2012      1      1      .      M      maj    .      .

2013      1       .      .      F      maj     1     .

2014      1       .      .      M     min     .     1

2012      1       .      .      F     maj     1     .

;

run;

The following table gives me exactly what I want: the counts for applied, interviewed, and hired by year.

Title "All Applied Interviewed Hired.";

proc tabulate data = job_apps format = comma10. out = job_app._counts;

  class year  / missing;

  var  applied interviewed hired;

  tables (applied interviewed hired) * sum, year / row=float misstext = "0" printmiss;

  keylabel n=' ';

run;

Title;

I would be very happy to have a new dataset (job_app_counts) that looks somewhat like this table, but the output dataset has 9 different values for year, and I can't figure out why. putting "year" in the rows of the table (instead of the columns) seemed to fix this, but when I tried to transpose the resulting dataset, it gave me something where year was now a value of the variables "COL1 COL2 COL3 COL4", which I don't understand.

Next, I want a table for the percentages of all who were interviewed (out of who applied), and all who were hired (out of who was interviewed). I have been trying to follow these instructions (http://www.pharmasug.org/proceedings/2011/PO/PharmaSUG-2011-PO16.pdf) but without much luck. I think part of it is that I have the variables in the var statement. This was the only way I could figure out how to get tabulate to compute the counts without deleting the missing values.

Finally, I want a table for women and minorities who applied interviewed or hired, without showing the men or majority applicants. I can't use a WHERE statement because some women are not minorities, etc. The following table gives me the format I'm looking for except that it shows the missing values. I can't suppress the missing values because then it only gives me the cases where fem_count AND min_count = 1.

Title "Female & Minority Applied Interviewed Hired";

proc tabulate data = job_apps format = comma10.;

  class fem_count min_count year / missing;

  var  applied interviewed hired;

  tables (fem_count min_count)* ((applied interviewed hired) * sum = "") all = "Total", year all = "Total" / row=float misstext = "0" printmiss;

  keylabel n=' ';

run;

Title;

Thank you for reading.

[Reeza]

Do you want data set's or output to the Result window?

Using proc tabulate to generate datasets in the format it gets displayed is cumbersome.

For the first one here's a way to do it, assuming you want a data set. It is a two step process because it requires a transpose.

proc means data=job_apps noprint nway;

var applied interviewed hired;

class year;

output out=_counts sum= ;

label applied = 'Applied' interviewed='Interviewed' hired='Hired';

run;

proc transpose data=_counts(drop=_:) out=want1 prefix=Year;

ID year;

run;