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;
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;
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;
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.);
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;
Sorry but I didn't get the desired result. Still see same order.
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;
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.