BookmarkSubscribeRSS Feed
shailey
Obsidian | Level 7

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! 

2 REPLIES 2
ballardw
Super User

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;
Ksharp
Super User

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; 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1056 views
  • 0 likes
  • 3 in conversation