topic Re: How can we sort data by PCT column from largest to smallest as it is computed column in proc rep in SAS Programming

How can we sort data by PCT column from largest to smallest as it is computed column in proc report

saikiran_nemani — Sat, 09 Sep 2023 05:14:50 GMT

TITLE height=3 color=BLUE J=LEFT "Branch Wise 1+ Count";
PROC REPORT DATA=WORK.FINAL_DPD LS=132 PS=60  SPLIT="/" CENTER ;
COLUMN  LOAN_BRANCH_BHEEM DPD TOTAL_COUNT PCT;
DEFINE  DPD / SUM FORMAT= 11. WIDTH=11    SPACING=2   CENTER "1+ Count" ;
DEFINE  LOAN_BRANCH_BHEEM / DISPLAY FORMAT= $150. WIDTH=150   SPACING=2   LEFT "Branch";
DEFINE 	TOTAL_COUNT / SUM CENTER "Total Count";
DEFINE  PCT/computed format = percent8.2 CENTER "% (Count)"; 

RBREAK AFTER/SUMMARIZE;
	COMPUTE AFTER;
	LOAN_BRANCH_BHEEM="Grand Total";
	if upcase(_BREAK_)="_RBREAK_" then do;
	call define (_row_,'style','style=[background=#EDF2F9 foreground=#112277 fontweight=bold');
	END;
	ENDCOMP;

	COMPUTE PCT; 
          PCT =_c2_/_c3_;           
	ENDCOMP;
RUN;

Re: How can we sort data by PCT column from largest to smallest as it is computed column in proc rep

Kurt_Bremser — Sat, 09 Sep 2023 05:23:09 GMT

Calculate the percentage in a preceding DATA step, so you can sort the result BY DESCENDING PCT.

Then run PROC REPORT without the COMPUTE.

Re: How can we sort data by PCT column from largest to smallest as it is computed column in proc rep

saikiran_nemani — Sat, 09 Sep 2023 06:03:48 GMT

Grand Total

30053

196653

Many Thanks @Kurt_Bremser as I am not able to get grand total against two columns as I am using display as an option.

Request to opinion on this.

Re: How can we sort data by PCT column from largest to smallest as it is computed column in proc rep

Kurt_Bremser — Sat, 09 Sep 2023 06:55:26 GMT

Pleas post usable example data which leads to this expected outcome, so I can verify any code suggestions.

Re: How can we sort data by PCT column from largest to smallest as it is computed column in proc rep

saikiran_nemani — Sat, 09 Sep 2023 08:59:10 GMT

Location	30+ Count	Total Count	% (Count)
Kakinada	199	447	44.52%
Jaipur	315	768	41.02%
Visakhapatnam	136	354	38.42%
Maharasthra	133	362	36.74%
Karimnagar	139	418	33.25%
Grand Total	922	2349

@Kurt_Bremser I am posting table which was asked earlier.

I have added datastep logic as per your suggestions.

But everything comes right as it got sorted but Grand Total is not coming as I am expecting 30+ Count/Total Count.

Re: How can we sort data by PCT column from largest to smallest as it is computed column in proc rep

Kurt_Bremser — Sat, 09 Sep 2023 17:15:04 GMT

Add the calculation to the COMPUTE block:

compute after;
  LOAN_BRANCH_BHEEM = "Grand Total";
  _c4_ = _c2_ / _c3_;
  if upcase(_BREAK_)="_RBREAK_" then do;
    call define (_row_,'style','style=[background=#EDF2F9 foreground=#112277 fontweight=bold');
  end;
endcomp;

Just an idea, untested.

Re: How can we sort data by PCT column from largest to smallest as it is computed column in proc rep

Tom — Sat, 09 Sep 2023 18:27:47 GMT

That is not data, looks like perhaps the report?
So first let's convert it into actual data so we have something to program with.

data have;
  input Location :$20. count total percent :percent.;
cards;
Kakinada  199 447 44.52%
Jaipur  315 768 41.02%
Visakhapatnam 136 354 38.42%
Maharasthra 133 362 36.74%
Karimnagar  139 418 33.25%
;

Now we can write PROC REPORT code. You can use the PERCENT column twice. Once to GROUP the report so you can use it to order and again so the value is displayed. You can use COMPUTE AFTER block to add the GRAND TOTAL row label and also to fix up the percent.

proc report data=have;
  column percent location count total percent=x_percent ;
  define percent / group descending order=internal noprint;
  define location / group order=data ;
  define count / sum '30+ Count' format=comma7.;
  define Total / sum 'Total Count' format=comma7.;
  define x_percent / '% (Count)' format=percent8.2 ;
  rbreak after / summarize;
  compute after;
    location='Grand Total';
    x_percent = (count.sum/total.sum);
    call define (_row_,'style','style=[background=#EDF2F9 foreground=#112277 fontweight=bold');
  endcomp;
run;

Result

If you don't want the percent of 30+ day values on the Grand Total line then set it to missing instead. You might want to set the system option MISSING to ' ' before hand so the value is not displayed as a period.