Hi, I's like to create a table where the analysis variables are displayed vertically. Some background: Some of these tables are broken out by several class variables that have missing values, so to circumvent that problem I've used PROC SUMMARY to summarize the data I care about and I'm using this summary dataset to create the table.
I've tried listing the columns in every possible order but they are always listed horizontally. The dataset is:
data job2;
input year apply_count interviewed_count hired_count interviewed_mean hired_mean;
datalines;
2012 349 52 12 0.149 0.23077
2013 338 69 20 0.20414 0.28986
2014 354 70 18 0.19774 0.25714
;
run;
Here's an example of the proc report code for just one analysis variable:
proc report data = job2;
columns apply_count year;
define year / across " ";
define apply_count / analysis "Applied" format = comma8.;
run;
Ideally the final report would look like this:
2012 2013 2014
Applied 349 338 354
Interv. 52 69 70
Hired 12 20 18
Inter % 15% 20% 20%
Hired % 23% 29% 26%
Also, is it possible to insert blank rows and columns? (this is for the purpose of copy/pasting into Excel) . Ideally there'd be a blank column in between each year.
Thank you!
Adding a dummy variable and Proc tabulate can do this:
data job2;
input year apply_count interviewed_count hired_count interviewed_mean hired_mean dummy;
datalines;
2012 349 52 12 0.149 0.23077 .
2013 338 69 20 0.20414 0.28986 .
2014 354 70 18 0.19774 0.25714 .
;
run;
proc tabulate data=job2;
class year;
var apply_count interviewed_count hired_count interviewed_mean hired_mean dummy;
table (apply_count interviewed_count hired_count)*sum=''*f=comma8.
dummy=''*sum=''
(interviewed_mean hired_mean) * mean=''*f=percent8.0
, year=''
/row=float misstext=' ';
label
apply_count = "Applied"
interviewed_count = "Interv."
hired_count = "Hired"
interviewed_mean = "Inter %"
hired_mean = "Hired %"
;
run;
Yes. you can . But you need transform your original data .
data job2;
input year apply_count interviewed_count hired_count interviewed_mean hired_mean;
datalines;
2012 349 52 12 0.149 0.23077
2013 338 69 20 0.20414 0.28986
2014 354 70 18 0.19774 0.25714
;
run;
proc transpose data=job2 out=temp;
idlabel year;
id year;
run;
data temp;
set temp;
if _n_ eq 4 then g+1;
run;
proc report data = temp nowd out=x;
columns g _name_ _2012 d1 _2013 d2 _2014;
define g/order noprint;
define _name_/display ' ';
define d1/computed ' ';
define d2/computed ' ';
compute d1/character length=10;
d1=' ';
endcomp;
compute d2/character length=10;
d2=' ';
endcomp;
compute _2012;
if find(_name_,'mean','i') then call define(_col_ ,'format','percent8.2');
endcomp;
compute _2013;
if find(_name_,'mean','i') then call define(_col_ ,'format','percent8.2');
endcomp;
compute _2014;
if find(_name_,'mean','i') then call define(_col_ ,'format','percent8.2');
endcomp;
compute before g;
len=ifn(g=1,10,0);x=' ';
line x $varying20. len;
endcomp;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.