Hello
Is it possible to use overlapping ranges(proc format) with proc summary procedure?
Yes. See the small example below
proc format library=work;
value OverLapFmt (multilabel)
low - 60 = 'Group 1'
55 - 70 = 'Group 2'
65 - high = 'Group 3'
;
run;
proc summary data=sashelp.class sum nway;
class height / mlf;
var weight;
format height OverLapFmt.;
output out=want sum=sum;
run;
Yes. See the small example below
proc format library=work;
value OverLapFmt (multilabel)
low - 60 = 'Group 1'
55 - 70 = 'Group 2'
65 - high = 'Group 3'
;
run;
proc summary data=sashelp.class sum nway;
class height / mlf;
var weight;
format height OverLapFmt.;
output out=want sum=sum;
run;
I am trying to use the example that you sent (thank you so much).
What is the reason that I get an error here
"ERROR: These two ranges overlap: 2<-3 and 2-4 (fuzz=1E-12)."
Data Loans;
format date date9.;
input CustomerID date:date9. Sum_Loan interest score;
cards;
1234 10Jan2019 10 2.1 4
1234 13Jan2019 20 2.2 4
1234 18Feb2019 30 1.9 3
2222 10Jan2019 40 3.5 3
3333 19Jan2019 50 3.7 2
4444 21Feb2019 60 2.9 2
5555 08Jan2019 70 8.0 2
5555 26Feb2019 80 7.0 2
;
run;
proc sort data=Loans;by CustomerID;run;
Data Loans2;
set Loans;
by CustomerID;
Retain Help 0;
if first.CustomerID then Help=Help+1;
Run;
Data Loans3;
Set Loans2;
interest1=interest;
Run;
proc format;
value ffmt
2='2'
3='3'
2-3='2--3'
4='4'
2-4='2-4'
5-11='5--11';
run;
proc summary data=Loans3 nway;
class score /mlf;
format score ffmt.;
var sum_loan interest;
var interest1/weight=sum_loan;
output out=pelet(drop=_type_ _freq_)
max(Help)=n_customers
n(Help)=nloans
sum(sum_loan)=Total_sum_loans
mean( interest1)=wgt_mean_interest
mean( interest)= mean_interest;
format n_customers: comma12.
nloans:comma12.
Total_sum_loans : comma12.
wgt_mean_interest :comma12.2
mean_interest :comma12.2;
run;
You have to specify the Mulitlabel Option in PROC FORMAT like this.
I haven't looked through the rest of your code, can't say if there is anything else.
proc format;
value ffmt (multilabel)
2='2'
3='3'
2-3='2--3'
4='4'
2-4='2-4'
5-11='5--11';
run;
Perfect!
Now it is working.
The last question is how can I control the order of the categories in the output.
In the output I see that the order is :
2
2--3
2-4
3
4
But I want that the order will be :
2
3
2--3
4
2-4
You can make force that the order of the output is like the order specified in PROC FORMAT by adding a few options. I'm building on the example I posted above to demonstrate
proc format library=work;
value OverLapFmt (multilabel notsorted)
55 - 70 = 'Group 2'
low - 60 = 'Group 1'
65 - high = 'Group 3'
;
run;
proc summary data=sashelp.class sum nway;
class height / mlf preloadfmt order=data;
var weight;
format height OverLapFmt.;
output out=want sum=sum;
run;
Controlling order of appearance with multilabel formats is not a trivial exercise.
The attached program document has the SAME values in different multilabel formats and displayed using different options in proc tabulate as some examples of how the format definition and report procedure options interact.
Code includes a small data set made with call to random functions.
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.