The SAS Output Delivery System and reporting techniques

Sorting of Data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Sorting of Data

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;

 

 


Accepted Solutions
Solution
‎06-09-2016 04:36 AM
Super User
Super User
Posts: 7,727

Re: Sorting of Data

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.

View solution in original post


All Replies
Super User
Posts: 7,465

Re: Sorting of Data

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)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 13

Re: Sorting of Data

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

Solution
‎06-09-2016 04:36 AM
Super User
Super User
Posts: 7,727

Re: Sorting of Data

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.

Occasional Contributor
Posts: 13

Re: Sorting of Data

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

 

 

Super User
Super User
Posts: 7,727

Re: Sorting of Data

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 349 views
  • 1 like
  • 3 in conversation