topic Proc Report with across and group, order of group varaible in SAS Programming

Proc Report with across and group, order of group varaible

Ronein — Wed, 28 Aug 2019 07:37:43 GMT

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;

Re: Proc Report with across and group, order of group varaible

Jagadishkatam — Wed, 28 Aug 2019 07:44:35 GMT

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;

Re: Proc Report with across and group, order of group varaible

Ronein — Wed, 28 Aug 2019 08:08:35 GMT

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.);

Re: Proc Report with across and group, order of group varaible

Jagadishkatam — Wed, 28 Aug 2019 08:13:07 GMT

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;

Re: Proc Report with across and group, order of group varaible

Ronein — Wed, 28 Aug 2019 08:16:31 GMT

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

Re: Proc Report with across and group, order of group varaible

Ronein — Wed, 28 Aug 2019 08:29:50 GMT

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;