overlapping ranges(proc format) with proc summary procedure

[Ronein]

Hello

Is it possible to use  overlapping ranges(proc format) with proc summary procedure?

[PeterClemmensen · Accepted solution]

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;

[Ronein]

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;

 

[PeterClemmensen]

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;

[Ronein]

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

[PeterClemmensen]

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;

[ballardw]

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.