Sorting Character varaiables in Proc Report

[Harish2]

We are generating yearly report for the client and having financial year from July to June... I have the Output result below.But I need that to be order by July 2016 to June 2017 by proc report only.Please find the below code and also output.Please help me resolve this issue.

Output:                      

Month

Apr-17

Aug-16

Jan-17

Jun-17

May-17

Nov-16

Oct-16

Sep-16

Jul-16

Dec-16

Feb-17

Mar-17

Please find the code below:

TITLE1 "Provider Closed Case Review";

TITLE2 "Average Number of Days Open per Month";

TITLE3 "For State Fiscal Year &FY.";

options missing=0 ;

proc report data= days_opened_final ;

columns case_opened_month NO_CLOSED_CASES AVR_DYAS ('_Type of Cases_' COMP ALR DRA ACC REC EXC DEATH PROVI PRE );

define case_opened_month / order descending order=formatted;

define case_opened_month / group "Month";

define case_opened_month1 /computed noprint;

define no_of_cases_closed/analysis sum "No of Cases Closed";

define AVR_DYAS /mean "Average days opened";

define COMP/analysis sum "Provider Comprehensive SURS Review";

define ALR/analysis sum "Algorithm Limited Review";

define DRA/analysis sum "DRA Attestation";

define ACC/analysis sum "MCO Review Outcome Accuracy";

define REC/analysis sum "State-Directed Recovery";

define EXC/analysis sum "Excluded Entity";

define DEATH/analysis sum "Beneficiary Death";

define PROVI/analysis sum "Provider Death";

define PRE/analysis sum "Prepayment Review";

compute case_opened_month;

IF _break_='_RBREAK_' THEN CALL DEFINE('case_opened_month','style','style=[pretext="Total"]');

endcomp;

rbreak after/summarize ol ul ;

run;

[Reeza]

Don't store dates as a character variable. Of course it's going to sort alphabetically which is correct because that's how you've specified it. 

Create a numeric variable with a date format and use that variable to order your data. Because of the variable conversion it needs to be done prior to the PROC REPORT. I don't think there's a way to fix it directly in the PROC. 

[Harish2]

Thanks Reeza for the qucik response..

Since the month varaible is in monyy7. format and i have tried by using Input function for changing the charatcer to numeric varaible but it is not giving desired result.

Please help me to give the solution /code for this to change.

Thanks,

Harish

[Reeza]

---

@Harish2 wrote:

Thanks Reeza for the qucik response..

Since the month varaible is in monyy7. format and i have tried by using Input function for changing the charatcer to numeric varaible but it is not giving desired result.

Please help me to give the solution /code for this to change.

Thanks,

Harish

---

This isn't informative. Why/How did INPUT() not work? 

Post the code you've tried and indicate where you're having issues. 

Monyy7 doesn't expect a dash AFAIK so either remove the dash using the COMPRESS() function or try ANYDTDTE format. 

[Harish2]

Hi Reeza,

Here is the code I am using to convert the format

data order;
set days_opened_final;
date_new = input(substr(case_opened_month, 1, 3) ||
substr(case_opened_month,4, 4), monyy7.);
run;

[Harish2]

Thanks Reeza..

Its worked by using ANYDTDTE format..