Sorting Character varaiables in Proc Report

Reply
Contributor
Posts: 21

Sorting Character varaiables in Proc Report

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;

 

Grand Advisor
Posts: 17,351

Re: Sorting Character varaiables in Proc Report

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. 

Contributor
Posts: 21

Re: Sorting Character varaiables in Proc Report

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

Grand Advisor
Posts: 17,351

Re: Sorting Character varaiables in Proc Report


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. 

Contributor
Posts: 21

Re: Sorting Character varaiables in Proc Report

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;
Contributor
Posts: 21

Re: Sorting Character varaiables in Proc Report

Thanks Reeza..

Its worked by using ANYDTDTE format..

Ask a Question
Discussion stats
  • 5 replies
  • 104 views
  • 1 like
  • 2 in conversation