Proc Report with across and group, order of group varaible

[Ronein]

Hello

In the following example I am using  proc report .

The problem  is in  the output table ,order of  categories  of  "Cat_Obligo"   field is not same as in proc format.

The order in output is :

Lower than 30K

[100,200)

[200,500)

[30,100)

[500+

But I want that the order will be same as in proc format:

 'Lower than 30K'
 '[30,100)'
 '[100,200)'
 '[200,500)'
 '[500+'

What is wrong?

Why did it happen?

Data RawData;
input ID  mon Obligo allowance;
cards;
/*enter Raw data */
Run;


proc format;
value ffmt    
  0 -< 30 = 'Lower than 30K'
  30 -< 100 = '[30,100)'
  100 -< 200 = '[100,200)'
  200 -< 500 = '[200,500)'
  500 - high = '[500+'
;
run;

data RawData2;
set RawData;
Cat_Obligo=put(Obligo,ffmt.);
run;

proc sort data=RawData2;by mon Obligo;
Run;

title;
PROC REPORT DATA=RawData2 NOWINDOWS; 
COLUMN ('Group of Obligo' Cat_Obligo ) mon,(Obligo  allowance     Obligo=N_Customers);
DEFINE Cat_Obligo  / '' GROUP  order=internal ; 
DEFINE mon/ '' ACROSS NOZERO  ORDER=INTERNAL;
DEFINE Obligo / SUM 'Sum_OBLIGO' FORMAT=comma12.1 ; 
DEFINE allowance  / SUM 'Sum_Allowance' FORMAT=comma12.1 ; 
DEFINE N_Customers /N 'Customers'  FORMAT=comma18.; ; 
RUN;

[Jagadishkatam]

The problem is you are not using the order in Obligo variable, please try the below code, Here i created a copy of Obligo  as Obligo2 which i used in the begining of the all variables in column statement of proc report and order it. Please test and let us know.

data RawData2 ;
set RawData2 ;
Obligo2=Obligo  ;
run;

PROC REPORT DATA=RawData2 NOWINDOWS; 
COLUMN Obligo2 ('Group of Obligo' Cat_Obligo ) mon,(Obligo  allowance     Obligo=N_Customers);
DEFINE Obligo2   / noprint order order=data; 
DEFINE Cat_Obligo  / '' GROUP  order=internal ; 
DEFINE mon/ '' ACROSS NOZERO  ORDER=INTERNAL;
DEFINE Obligo / SUM 'Sum_OBLIGO' FORMAT=comma12.1 ; 
DEFINE allowance  / SUM 'Sum_Allowance' FORMAT=comma12.1 ; 
DEFINE N_Customers /N 'Customers'  FORMAT=comma18.; ; 
RUN;

[Ronein]

Thank you

I don't see where you use ffmt format.

In the creation of Rawdata2 you missed the statement

Cat_Obligo=put(Obligo,ffmt.);

[Jagadishkatam]

I am assuming that  the Cat_Obligo=put(Obligo,ffmt.); is alreadu created in Rawdata2, so in the same dataset I am creating a new variable  Obligo2 only for sorting. Then i am using that in proc report.

data RawData2 ;
set RawData2 ;
Obligo2=Obligo  ;
run;

PROC REPORT DATA=RawData2 NOWINDOWS; 
COLUMN Obligo2 ('Group of Obligo' Cat_Obligo ) mon,(Obligo  allowance     Obligo=N_Customers);
DEFINE Obligo2   / noprint order order=data; 
DEFINE Cat_Obligo  / '' GROUP  order=internal ; 
DEFINE mon/ '' ACROSS NOZERO  ORDER=INTERNAL;
DEFINE Obligo / SUM 'Sum_OBLIGO' FORMAT=comma12.1 ; 
DEFINE allowance  / SUM 'Sum_Allowance' FORMAT=comma12.1 ; 
DEFINE N_Customers /N 'Customers'  FORMAT=comma18.; ; 
RUN;

[Ronein]

Sorry but I didn't get the desired result. Still see same order.

[Ronein · Accepted solution]

The solution that worked well is adding  a.  b. c.  d.  e. to proc format categories and then use order=formatted

Data RawData;
input ID  mon Obligo allowance;
cards;
/*enter Raw data */
Run;


proc format;
value ffmt    
  0 -< 30 = 'a.Lower than 30K'
  30 -< 100 = 'b.[30,100)'
  100 -< 200 = 'c.[100,200)'
  200 -< 500 = 'd.[200,500)'
  500 - high = 'e.[500+'
;
run;

data RawData2;
set RawData;
Obligo2=Obligo  ;
Cat_Obligo=put(Obligo,ffmt.);
run;

proc sort data=RawData2;by mon Obligo;
Run;

title;
PROC REPORT DATA=RawData2 NOWINDOWS; 
COLUMN ('Group of Obligo' Cat_Obligo ) mon,(Obligo  allowance     Obligo=N_Customers);
DEFINE Cat_Obligo  / '' GROUP  order=formatted ; 
DEFINE mon/ '' ACROSS NOZERO  ORDER=INTERNAL;
DEFINE Obligo / SUM 'Sum_OBLIGO' FORMAT=comma12.1 ; 
DEFINE allowance  / SUM 'Sum_Allowance' FORMAT=comma12.1 ; 
DEFINE N_Customers /N 'Customers'  FORMAT=comma18.; ; 
RUN;