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;
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
Tried using the order keyword, but didn't get the desired result. Thanks for your assistance anyway..
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.