How to create a table of nested values and percentages

shailey — Fri, 13 Feb 2015 15:35:33 GMT

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.

Re: How to create a table of nested values and percentages

Reeza — Fri, 13 Feb 2015 17:04:37 GMT

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;

topic How to create a table of nested values and percentages in SAS Procedures

How to create a table of nested values and percentages

Re: How to create a table of nested values and percentages