topic Re: overlapping ranges(proc format) with proc summary procedure in SAS Programming

overlapping ranges(proc format) with proc summary procedure

Ronein — Tue, 11 Jun 2019 05:00:08 GMT

Hello

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

Re: overlapping ranges(proc format) with proc summary procedure

PeterClemmensen — Tue, 11 Jun 2019 06:01:08 GMT

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;

Re: overlapping ranges(proc format) with proc summary procedure

Ronein — Tue, 11 Jun 2019 06:14:13 GMT

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;

Re: overlapping ranges(proc format) with proc summary procedure

PeterClemmensen — Tue, 11 Jun 2019 06:23:47 GMT

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;

Re: overlapping ranges(proc format) with proc summary procedure

Ronein — Tue, 11 Jun 2019 06:38:04 GMT

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-4

Re: overlapping ranges(proc format) with proc summary procedure

PeterClemmensen — Tue, 11 Jun 2019 06:45:47 GMT

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;

Re: overlapping ranges(proc format) with proc summary procedure

ballardw — Wed, 12 Jun 2019 15:30:11 GMT

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.