topic Re: Ordering in a proc Report in SAS Enterprise Guide

Ordering in a proc Report

DME790 — Fri, 09 Mar 2018 03:25:18 GMT

Hi All,

I would like to order/sort in a proc report based on a computed column (TransPCT).

Any help appreciated - cheers

Dean

data test;
	Input Origin $ Target_Cluster $	Target_Code $ TransCount;
	Datalines;
AA DDD 1A 97
BB DDD 1B 1
CC EEE 2A 55
DD FFF 3A 1
EE FFF 3B 2
FF FIS 4A 1
GG OTH 5A 1
HH OTH 5B 1
II TPD 6A 2
JJ TPD 6B 1
;
Run;

Proc Report data=Test;
	Column ORIGIN TARGET_CLUSTER TARGET_CODE TRANSCOUNT TransPCT;
	Define Origin / DISPLAY ;
	Define Target_Cluster / DISPLAY 'Target Cluster';
	Define Target_CODE / DISPLAY 'Target Queue';
	Define TRANSCOUNT / DISPLAY 'Count' Analysis sum;
	Define TransPCT / Computed 'Percent' f=percent8.2;

		Compute Before;
			TransTOT = TransCount.SUM;
				ENDCOMP;

		compute TransPCT;
			TransPCT = TransCount.SUM / TransTOT;
				ENDCOMP;

Rbreak after / Summarize;

RUN;

Current output looks like this

Origin	Target Cluster	Target Queue	Count	Percent
AA	DDD	1A	97	59.88%
BB	DDD	1B	1	0.62%
CC	EEE	2A	55	33.95%
DD	FFF	3A	1	0.62%
EE	FFF	3B	2	1.23%
FF	FIS	4A	1	0.62%
GG	OTH	5A	1	0.62%
HH	OTH	5B	1	0.62%
II	TPD	6A	2	1.23%
JJ	TPD	6B	1	0.62%
			162	100.00%

Output required

Origin	Target Cluster	Target Queue	Count	Percent
AA	DDD	1A	97	59.88%
CC	EEE	2A	55	33.95%
EE	FFF	3B	2	1.23%
II	TPD	6A	2	1.23%
BB	DDD	1B	1	0.62%
DD	FFF	3A	1	0.62%
FF	FIS	4A	1	0.62%
GG	OTH	5A	1	0.62%
HH	OTH	5B	1	0.62%
JJ	TPD	6B	1	0.62%
			162	100.00%

Re: Ordering in a proc Report

reshmape — Fri, 09 Mar 2018 10:06:33 GMT

Hi,

I think we use proc report mainly for reporting the data.If you want to sort the data better to create a data set for finding TransPCT

and sort it by 'descending TransPCT'.

Re: Ordering in a proc Report

ballardw — Fri, 09 Mar 2018 21:47:18 GMT

Proc Report builds the output left to right per the columns in the column statement. If the major sort order is to be TransPCT it would have to be the first column.

Order variables (to control sorting) have to be dataset variables. So to accomplish what you want you would need to calculate transpct before proc report. At which point you could likely calculate transtot as well.

Or possibly consider:

proc freq data=test order=freq noprint;
   tables Origin* Target_Cluster*	Target_Code/list  out=work.sum;
   weight transcount;
run;

and use WORK.SUM as the input to proc report. The order is already correct.

or Proc print with the sum option

proc print data=work.sum noobs label;
   sum count percent;
run;