The SAS Output Delivery System and reporting techniques

How do I sort in proc report?

Reply
Occasional Contributor
Posts: 5

How do I sort in proc report?

Hi

I am using the below code to create an excel file. However I want to sort the output by the highlighted color "gold" & then put only those highlighted rows on top of the report output as in the attached "desired report".

The code below gives me the relevant rows highlighted but does not sort by the color & put it on top of the spreadsheet.

Please help Smiley Happy

 

/* CREATE OTP FEEDBACK SPLIT SPREADSHEET PER EA IN DIRECTORY */

ods _all_ close;

ods excel file="\\105hlsasdbpr01\DATA01\MI_TEAM_STORAGE\TEAM\Collections & Recoveries\NAS\Daily\OTP Feedback\Latest Report\Mtrack OTPFeedback.xlsx"

options (sheet_name="OTP_FeedbackAll" frozen_headers='yes' absolute_row_height='15');

proc report data=work.OTP_Feedback1 missing headline nocenter

style(header) = [background=green foreground=white];

column EstateAgent AccountNumber ClientName OTPSubmittedDate OTPStatus OTPValue OTPSLAdays CancellationDate BuyerName BuyerFinance BFOutcome BFSLAdays

 

FinalBondProvider TransferAttorneys Comments PropertyDetails Commission CommPercentage EABranch DateOTPAccepted OTPSubmitMonth LeadSource;

 

define EstateAgent / display 'EstateAgent' style(column)=data[ width=15% tagattr='wrap:no'] center;

define AccountNumber / display style(column)=[cellwidth=3cm];

define ClientName / display style(column)=data[ width=40% tagattr='wrap:no'] left ;

define OTPSubmittedDate / display style(column)={tagattr="wrap:no"} right;

define OTPStatus /display 'OTPStatus' style(column)=[cellwidth=2cm] center;

define OTPValue / display style(column)=[cellwidth=2.5cm] format=comma12.2 right;

define OTPSLADays / display style(column)={tagattr="wrap:no"} center;

compute CancellationDate;

if CancellationDate = . then

call define(_row_,"style","style={background=gold}");

endcomp;

define CancellationDate / display style(column)={tagattr="wrap:no"} right;

define BuyerName /display style(column)=data[ width=20% tagattr='wrap:no'] left ;

define BuyerFinance / display 'BuyerFinance' style(column)={tagattr="wrap:no"} left;

define BFOutcome /display 'BFOutcome' style(column)={tagattr="wrap:no"} width=5 left;

define BFSlAdays / display width=2 center;

define FinalBondProvider /display 'FinalBondProv' style(column)={tagattr="wrap:no"} left;

define TransferAttorneys /display 'TransferAttorneys' style(column)={tagattr="wrap:no"} left;

define Comments / style(column)={tagattr="wrap:no"} left;

define PropertyDetails / style(column)={tagattr="wrap:no"} left;

define Commission / display width=4 format=comma12.2 right;

define CommPercentage / display style(column)=[cellwidth=3cm];

define EABranch /display width=10 left;

define DateOTPAccepted /display style(column)={tagattr="wrap:no"} right;

define OTPSubmitMonth / display style(column)={tagattr="format:yyyymm"} center;

define LeadSource / display style(column)=[cellwidth=4cm] left;

run;

ods excel close;

 

Super User
Super User
Posts: 9,840

Re: How do I sort in proc report?

Pleas use the code window - its the {i} above post area - in future for posting code as it retains code formatting - helps to read the code.  Ordering in proc report is done by using the order keyword:

define CancellationDate / order order=internal display style(column)={tagattr="wrap:no"} right;

You can change the parameter after the order=.  Here is a paper on it:

http://support.sas.com/resources/papers/proceedings11/090-2011.pdf

 

Occasional Contributor
Posts: 5

Re: How do I sort in proc report?

Tried using the order keyword, but didn't get the desired result. Thanks for your assistance anyway..

Ask a Question
Discussion stats
  • 2 replies
  • 100 views
  • 0 likes
  • 2 in conversation