topic Re: Order of rows in Proc Report in SAS Procedures

Order of rows in Proc Report

Ronein — Wed, 11 Jul 2018 12:21:57 GMT

Hello

I am using proc report to display data and calculate a new field.

I want that the rows in the output of Proc Report will be ordered by value of total_x (descending

 Data tbl;
 input branch X1  X2  X3  X4;
 cards;
 1  10 20 30 40 
 2  15 20 25 30
 3  35 40 45 50
 4  55 60 65 .
 ;
 run;


proc report data=tbl  headline split='#' spacing=1 nowd
style(report)={cellpadding=1 cellspacing=1}
style(header)={font_size=2  font_weight=MEDIUM  font_face=david  background=cx99ccaa}
style(column)={font_size=1}; 
columns  branch X1  X2  X3  X4  total;
define branch/ display 
	"Branch"  center;
define X1/  Display "X1 amount";
define X2/  Display "X2 amount";
define X3/  Display "X3 amount";
define X4/  Display "X4 amount";
define total/computed order=internal descending ;  
compute total;  
total=sum(x1,x2,x3,x4); 
endcomp;
Run;

)

Re: Order of rows in Proc Report

Kurt_Bremser — Wed, 11 Jul 2018 12:57:57 GMT

The log says it all. To be more specific, you can't use a computed value for ordering, as ordering in proc report takes place before values are computed.

Create the variable first, and then use it for ordering in proc report:

Data tbl;
input branch X1  X2  X3  X4;
total = sum(x1,x2,x3,x4);
cards;
1  10 20 30 40 
2  15 20 25 30
3  35 40 45 50
4  55 60 65 .
;
run;


proc report data=tbl  headline split='#' spacing=1 nowd
style(report)={cellpadding=1 cellspacing=1}
style(header)={font_size=2  font_weight=MEDIUM  font_face=david  background=cx99ccaa}
style(column)={font_size=1}; 
columns  branch X1  X2  X3  X4  total;
define branch/ display 
	"Branch"  center;
define X1/  Display "X1 amount";
define X2/  Display "X2 amount";
define X3/  Display "X3 amount";
define X4/  Display "X4 amount";
define total/order descending;
Run;