SAS formatting techniques: using Proc Format to create custom formats
SAS user-defined formats allow you to assign labels to the values of variables. Formats can be assigned to a character or numeric variables.
SAS user-defined formats allow you to assign labels to the values of variables.
PROC FORMAT in SAS can be used to assign formats or Informats to a character or numeric variables.
For example, if gender is a numeric variable with 1 for males and 2 for females, the user-defined format can be set up so that the values “male” and “female” are printed in SAS output, rather than 1 and 2.
The underlying variables are still numeric, so the same variables can also be used in numeric procedures, such as PROC REG, PROC MEANS, or PROC FREQ.
There are also default SAS formats, such as formats for date variables, that can be used at any time.
This article covers only some of the more basic uses of SAS user-defined formats for numeric variables.
User-defined formats are stored in a separate file called a formats catalog and are not part of a SAS data set.
Rules for defining FORMAT NAME
- For character values, the first character must be a dollar sign ($) and a letter or underscore as the second character. The name must have a letter or underscore for numeric values as the first character.
- A name cannot end with a number.
- Cannot be the name of an existing SAS format
- Should not end with a period in the VALUE statement
Basic Example of PROC FORMAT
The most common way of labelling data is simply assigning each unique code its label. We have taken the example from the SASHELP.PRICEDATA dataset.
We would like to assign region names the USA, EUROPE and ASIA corresponding to the values of 1,2 and 3.
Proc Freq procedures to find out the unique values of the region as below.
proc freq data=sashelp.pricedata; table region; run;
As you can see from the PROC FREQ output, the region column has values of 1,2 and 3.
The PROC FORMAT to assign region names to values is as below.
proc format; value RegionFmt 1=USA 2=EUROPE 3=ASIA ; run;
After running, the above code, check the log to see if the format is created.
You can apply the RegionFmt in any SAS dataset or Proc steps.
proc print data=sashelp.pricedata(obs=5); var date sale price region; format region RegionFmt.; run;
Creating formats for Multiple values
Beyond just a simple one-to-one mapping as illustrated above, we can also map multiple values to a single label. Ranges can be multiple values separated by commas.
proc format; value line 1,2,3= "Home Appliances" 4,5= "Clothing" ; run;
Creating formats with numeric ranges
In addition, we can use ranges and lists of values to create formats. For example, we can apply a single ‘label’ to ranges of values with code like this:
proc format; value weight 0 - 18 = "Underweight" 18.1 - 24.9 = "Normal weight" 25 - 29.9 = "Overweight" ; run;
Creating formats with HIGH and LOW
Ranges can include or exclude the bounding values, depending on using keywords like HIGH or LOW.
proc format; value weight low - < 18 = "Underweight" 18 - 24.9 = "Normal weight" 25 - 29.9 = "Overweight" 30 < - high = "Obesity" other = "No Records" ; run;
LOW refers to the least available number, HIGH refers to the highest available number, and OTHER includes all numbers not specified. Others also include missing values if it is not specified.
Creating Character Formats
Value labels can also be applied to character/string data values. The most important differences are:
- The name of the format must start with a dollar sign ($)
- The code values (on the left of the equals signs) must be quoted.
proc format; value $genderFmt 'M'='Male' 'F'='Female' Other='Error' ; run; data class; length sex $10.; set sashelp.class; format sex $genderfmt.; run;
We can also apply the basic principles of ranges to character formats. We could create the following
proc format; value $alphabets 'A' - <'K' = 'First 10' 'K' - <'U' = 'Second 10' 'U' - high = 'Remaining' Other = 'Error' ; run; data a1; set a; format var1 $alphabets.; run;
In the above example, Any values which come after A (including A) and before K (not including K), in an alphabetical sort, will be labelled as ‘First 10’.
Any values which come after K (including K) and before U (not including U), in an alphabetical sort, will be labelled as ‘Second 10’.
Any values which come after ‘U’ (including U) will be labelled as ‘Remaining’. All other values (including missing) will be displayed as ‘Error ‘.
In an alphabetical sort, SAS sorts numeric values before A. So, in this example, missing values and any values beginning with numbers will be labelled as ‘Error ‘.
You can also include the name of a SAS format, user-defined format, or informat rather than a text string in place of a label. Here is an example:
You want to read dates from July 15, 2005, to December 31, 2006, using the mmddyy10. informat. Dates before July 15, 2005, should be formatted as “Not Open”, and dates after December 31, 2006, should be formatted as “Too Late.”
You can use nested formats as follows to accomplish this task:
proc format; value registration low - <'15Jul2005'd = 'Not Open' '15Jul2005'd - '31Dec2006'd = [date9.] '01Jan2007'd - high = 'Too Late' ; run;
Under normal circumstances, you get an error message if any format ranges overlap. However, if you use the multilabel option on the VALUE statement, you can create a format with overlapping ranges.
Certain multilabel-enabled procedures can then use the multilabel format to produce tables showing all format ranges.
Here is an example:
You want to see the variable Age (from the SURVEY data set) broken down two ways—one in 20-year intervals, the other by a split at 50 years old. You first create a multilabel format like this:
proc format; value agegroup (multilabel) 0 - <20 = '0 to <20' 20-<40 ='20 to <40' 40-<60 ='40 to <60' 60-<80 ='60 to <80' 80 - high = '80 +' 0 - <50 = 'Less than 50' 50 - high = '< or = to 50'; run;
Creating informats is similar to creating formats, except the VALUE statement is replaced with an INVALUE statement.
One important distinction is that the right-hand side of the equals sign in a VALUE statement (the ‘label’ side) for a format must be a character.
In creating an informat, the right-hand side of the equals sign in the INVALUE statement can be numeric.
proc format; invalue convert 'A+' = 100 'A' = 96 'A-' = 92 'B+' = 88 'B' = 84 'B-' = 80 'C+' = 76 'C' = 72 'F' = 65; run; data grades; input @1 ID $3. @5 Grade grades.; datalines; 001 A 002 B+ 003 F 004 C+ 005 A ;
The name of the informat is followed by the INVALUE keyword, and a dollar sign is used to create the character informat.
Even though you are reading character values, the resulting data will be a numeric variable.
If you used the name $marks instead of marks as in this program, the variable marks would be a character.
proc format; invalue $marks 100 = 'A+' 96 = 'A' 92 = 'A-' 88 = 'B+' 84 = 'B' 80 = 'B-' 76 = 'C+' 72 = 'C' 65 = 'F' ; run; data marks; input @1 ID $3. @5 marks $marks.; datalines; 001 96 002 88 003 65 004 76 005 96 ; run;
There are some useful options that you can use when you create your informats. UPCASE and JUST are two such options.
INFORMAT options UPCASE and JUST
UPCASE, as the name implies, will convert the data values to uppercase before checking on the informat ranges.
JUST will left-align character values.
data grades; input @1 ID $3. @5 Grade convert2.; datalines; 001 A 002 B+ 003 F 004 C+ 005 A ;
Generally, it is best to use a format when converting from numeric to character. Informats are better suited to converting character to numeric or character to character.
Using an informat to convert from numeric to a character and/or numeric to numeric will generate a Note:
NOTE: Numeric values have been converted to character values”
Using numeric informat to read a combination of character and numeric data.
proc format; invalue readtemp(upcase) 96 - 106 = _same_ 'N' = 98.6 other = .; run; data temperatures; input Temp : readtemp5. @@; datalines; 101 N 97.3 n N 67 104.5 ;
The UPCASE option is used to convert any character to uppercase. The keyword _SAME_ option keeps any numeric values in the range of 96 to 106 like it is.
Values of ‘N’ are converted to the numeric value of 98.6, and any values not in the range 96 to 106 or equal to ‘N’ are set to a numeric missing value.
Using Existing Formats/Informats
Using a format is fairly simple. There are two rules to remember:
- If we are using a format, we use the PUT() statement and using an informal; we use the INPUT() statement
- We must use a character format for character variables, and for numeric variables, we must use a numeric format.
The variable is a character or numeric variable, and the formats are a SAS or user-defined format. The result of a PUT function is always a character value.
Given a numeric variable called region and a numeric format region format in sashelp.pricedata, to use the format to create a new variable, the code would look like this:
Similarly, for a character variable called sex and a character format $genferfmt., to use the format to create a new variable, the code would look like this:
Similar code works for informats, replacing the PUT() with an INPUT(). We can also apply a format/informat directly to a variable using the FORMAT/INFORMAT statement.
format racecd racef.; or format sexcd $sexf.;
The FORMAT/INFORMAT statement can be used in a DATA step and most PROCS. Always use the formatted value of the variable for the analysis process in PROC steps.
Viewing Existing Formats
Once we have located all existing format catalogs, we are most likely curious to see what formats are in these catalogs.
The following code can be used to do that:
proc format library=work fmtlib; run;
This code will print a map of the existing format catalog to the LST file. A small sample might look like this:
Saving User-Defined Formats in a SAS Formats Catalog:
SAS formats catalogs are saved with the extension as sas7bcat. You can save user-defined formats permanently.
If we don’t specify the catalog, SAS stores formats in the WORK library in a FORMATS catalogs and like other datasets of the WORK library, they also get deleted at the end of the session.
To save User-defined formats, you need to specify where to store the catalog and what to call it. This can be achieved by storing formats in a library other than WORK.
- First of all, we have to define a library.
LIBNAME fmts “/home/9to5sas/formats”;
- Use the library option in PROC format and provide a library name with the format filename. The file name must be a valid SAS dataset name.
proc format library=fmts; value $genderFmt 'M'='Male' 'F'='Female' Other='Error' ; run;
How to Use stored Format?
Now, whenever you want to use stored format, you have to tell SAS to look for formats in that catalog file. This is done with the
fmtsearch option. So before using it, we need to write a statement.
Options fmtsearch = (Library_Name.MYFILENAME)
Options fmtsearch = (fmts);
Creating formats from SAS datasets
In PROC FORMAT, we have an option CNTLIN, that allows us to create a format from a data set rather than a VALUE statement. Before using this option, we first look at the guidelines below:-
- The input dataset must contain three variables required by PROC FORMAT – START, LABEL and FMTNAME.
- Here START is the key field between these two tables, and it must be unique in the input dataset (Dataset we use in the CNTLIN option).
- The LABEL variable is the value we want to map to another dataset.
- The FMTNAME variable is the format’s name and must be passed in single quotes.
- After defining the format, we can use the put function to create a variable in the dataset based on the key field and the format we have defined.
data control; set countryCodes(rename= (AlphaCode = Start Country = Label)); retain Fmtname '$ALPHAFMT' Type 'C'; run;
The RENAME= data set option is used to rename the Alpha code to
START and Country to
<a class="rank-math-link" href="https://9to5sas.com/retain-in-sas/">RETAIN</a> statement is used to set
TYPE equal to
'C' for Character type format.
Using a RETAIN statement is more efficient than an assignment statement since these values are set at compile-time—an assignment statement executes each iteration of the DATA step.
proc datasets lib=mylib memtype=data; modify class; attrib _all_ label=' '; attrib _all_ format=; contents data=work.class; run; proc format cntlin=control fmtlib; run; proc datasets lib=mylib memtype=data; modify class; attrib _all_ label=' '; attrib _all_ format=; contents data=work.class; run;
Thank you!! Really informative and helpful.
Hi nice website https://google.com