How do I sort in proc report?

[DeepaG]

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

/* 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;

[RW9]

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

[DeepaG]

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