You can easily generate a user-defined format if the values that you want to format are in Microsoft Excel.

The sample code on the Full Code tab uses Base SAS® to convert an Excel file to a SAS data set containing the three required variables for a control data set: FMTNAME, START, and LABEL.  PROC FORMAT generates a format from the data set using the CNTLIN option. The START values obtained from the spreadsheet are grouped into age categories, and PROC REPORT is used to display the formatted results.

For another example of building a user-defined format from a SAS data set, see Example 11: Creating a Format from a Data Set in the Base SAS 9.4 Procedures Guide.

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

This example reads an Excel file and uses the DATA step to generate a control data set as the input to the CNTLIN option in PROC FORMAT. The new format is used in the PROC REPORT procedure to display average height in the ranges specified by the format.

---

/* Create an Excel spreadsheet for the example. */                                                                                       
proc export data=sashelp.class outfile=test                                                                                             
     dbms=xlsx outfile='c:\temp\exceltest.xlsx' replace;                                                                                   
run;                                                                                                                                    
                                                                                                                                        
/* Read the Excel spreadsheet and create a SAS data set. */                                                                             
proc import datafile='c:\temp\exceltest.xlsx' out=work.testfmt dbms=xlsx replace;                                                       
run;                                                                                                                                    
 
/* Remove duplicate values */
proc sort data=testfmt nodupkey;                                                                                                        
   by age;                                                                                                                                
run;                                                                                                                                    
                                                                                                                                        
/* Build control data set */                                                                                                            
data new(rename=(age=start) keep=age fmtname label);                                                                                    
   retain fmtname 'testfmt';                                                                                                 
   length label $8.;                                                                                                                    
   set testfmt;                                                                                                                         
   if age <= 12 then label='Pre-teen';                                                                                                  
   else if age ge 13 and age < 20 then label='Teen';
   else label='Adult'; 
run;                                                                                                                                    
                                                                                                              
/* Create the format using the control data set. */                                                                                     
proc format cntlin=new;                                                                                                                 
run;                                                                                                                                    
                                                                                                                                        
/* Display average height by formatted values */                                                                                
title;                                                                                                                                  
ods listing close;                                                                                                                      
ods html file="c:\avgheight.htm" style=styles.sasweb;                                                                                         
                                                                                                                                        
proc report data=sashelp.class nowd split='/';                                                                                                    
   title 'Using Control Data Set to generate a format with Excel file';                                                                 
   col age height n;  
   define n / 'Count'; 
   define age / group;                                                                                                       
   define height / mean 'Average Height /in Inches' f=6.2;   
format age testfmt.; 
run;                                                                                                                                    
                                                                                                                                        
ods html close;                                                                                                                         
ods listing;     

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Click to view sample output.