topic Re: SAS PROC REPORT how to display analysis variables as rows? (and how to insert blank rows/columns in ODS and Base Reporting

SAS PROC REPORT how to display analysis variables as rows? (and how to insert blank rows/columns)

shailey — Fri, 18 Dec 2015 20:36:14 GMT

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!

Re: SAS PROC REPORT how to display analysis variables as rows? (and how to insert blank rows/columns

ballardw — Fri, 18 Dec 2015 21:54:58 GMT

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;

Re: SAS PROC REPORT how to display analysis variables as rows? (and how to insert blank rows/columns

Ksharp — Mon, 21 Dec 2015 03:14:41 GMT

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;