topic Re: Sorting of Data in ODS and Base Reporting

Sorting of Data

aarathi — Wed, 08 Jun 2016 08:55:29 GMT

Hi every one,

I have sorted the data in table and it is giving the correct output. But the same is not showing correctly in the PDF output. Kindly resolve.

This is my code.
data Work.Want;
set Work.ZHSummary;
array nums _numeric_;
do over nums;
if nums=. then nums=0;
end;

Run;

proc report data=Work.Want

style(header)=[bordercolor=BLACK background=#97291E foreground=white font=(zurichbt,6pt) font_weight=bold ]
style(report)=[bordercolor=black borderwidth=1pt font=(zurichbt,6pt)]
style(column)=[background=#FFFFFF bordercolor=#000000 font=(zurichbt,5pt) vjust=middle cellheight= 0.10in]
style(summary)=[font_face=White] center split='_';
column('ZH & Bucket wise Performance :' ZHName)('Bucket' BKT)('TotalBook' TBCount TBPos)('Renewal' TRCount TRPos)('Run Down' TCCount TCPos)('Opportunity to renewal' NR1Count NR1Pos NR1Elb)('Potential Auction cases' NR2Count NR2Pos NR2Elb)('Renewal %' TPCount TPPos)('Run Down%' Closurecount ClosurePos)('Resolution %' TotalCount TotalBook);

define ZHName / 'ZH Name' group;
define BKT / 'Bucket' group;
break after ZHName /summarize ol skip style=[background=darkgray foreground=black bordercolor=black font=(zurichbt,5pt)font_weight=bold];

define TBCount/ 'Total Count' sum center;
define TBPos/ 'Total Book' sum format=BESTX9.1 center;
define TRCount/ 'Count' sum center;
define TRPos/ 'POS' sum format=BESTX9.1 center;
define TCCount/ 'Count' sum center;
define TCPos/ 'POS' sum format=BESTX9.1 center;
define NR1Count/ 'Count' sum center;
define NR1Pos/ 'POS' sum format=BESTX9.1 center;
define NR1Elb/ 'Elb' sum format=BESTX9.1 center;
define NR2Count/ 'Count' sum center;
define NR2Pos/ 'POS' sum format=BESTX9.1 center;
define NR2Elb/ 'Elb' sum format=BESTX9.1 center;
define TPcount/ 'count' computed format=Percent11.1 center;
define TPPos/ '% of Book' computed format=Percent11.1 center;
define Closurecount/ 'count' computed format=Percent11.1 center;
define Closurepos/ '% of Book' computed format=Percent11.1 center;
define TotalCount/ '% of count' computed format=Percent11.1 center;
define TotalBook/ '% of Book' computed format=Percent11.1 center;

compute TPcount;
TPcount=TRCount.sum/TBCount.sum;
endcomp;
compute TPPos;
TPPos=TRPos.sum/TBPos.sum;
endcomp;
compute Closurecount;
Closurecount=TCCount.sum/TBCount.sum;
endcomp;
compute Closurepos;
Closurepos=TCPos.sum/TBPos.sum;
endcomp;
compute TotalCount;
TotalCount=(TRCount.sum + TCCount.sum)/TBCount.sum;
endcomp;
compute TotalBook;
TotalBook=(TRPos.sum + TCPos.sum)/TBPos.sum;
endcomp;

/*rbreak after /summarize;

compute after;
if _break_ = '_RBREAK_' then do ZHName="Total";
call define (_row_,'style', 'style=[background=CXFF8000 foreground=Black bordercolor=black font=(zurichbt,5pt)font_weight=bold]' );
end;endcomp;*/run;title1;

Re: Sorting of Data

Kurt_Bremser — Wed, 08 Jun 2016 09:12:48 GMT

a) you did not include the SORT code for Work.ZHSummary

b) hint: use the code window (little running man symbol) to post SAS code, as it will be much easier to read/comprehend. Similarly, use the {i} button to post other text that needs a fixed-width font (log output eg)

Re: Sorting of Data

aarathi — Wed, 08 Jun 2016 09:15:18 GMT

But i have already sorted the data in the table. i don't want to sort it again.

Re: Sorting of Data

RW9 — Wed, 08 Jun 2016 09:40:22 GMT

The problem is that whilst you may have a dataset which is sorted before the proc report, you are applying grouping within the proc report which intrinsically sorts the data:

define ZHName / 'ZH Name' group;
define BKT / 'Bucket' group;

This will automatcially sort the data per the grouping criteria. Now I personally never use grouping and calculations in a proc report - I prefer to create a dataset as I want to see in the output, assigning ordersa and page breaking etc. Then the proc report is simple, and you can use:

define <variable> / order order=data;

This sorts the data as it appears in the dataset. I don't think that is available for groups.

So my suggestion is to remove the group/computes, and do that in the datastep before the proc report.

Re: Sorting of Data

aarathi — Wed, 08 Jun 2016 10:02:00 GMT

This is working . but i am not able to use the break statement .

Re: Sorting of Data

RW9 — Wed, 08 Jun 2016 10:16:44 GMT

You mean this line:

break after ZHName /summarize

Yes, you can't summarize on order data. As I said in my post I don't do calculating in proc reports. I do all calculations manipulations and such like in a datastep before the proc report, so the output dataset looks exactly as I want it, then I proc report that.

Cynthia may be able to help, I will move your post over to ODS section, but me personally I don't like data processing in a output procedure.