THE POWER OF PROC FORMAT - University of California, Los ...

1 ABSTRACTThe FORMAT procedure in SAS is a very powerfuland productive tool. Yet many beginning program-mers rarely make use of them. The FORMAT pro-cedure provides a convenient way to do a tablelookup in SAS. User-generated SAS Formats canbe used to assign descriptive labels to data vales,create new variables and find unexpected FORMAT can also be used to generate dataextracts and to merge data sets. This paper willprovide an introductory look at PROC FORMAT forthe beginning user and provide sample code ( ) that will illustrate the POWER of PROC FORMATin a number of FORMAT is a procedure that creates map-pings of data values into data labels.

2 The user de-fined FORMAT mapping is independent of a SASDATASET and variables and must be explicitly as-signed in a subsequent DATASTEP and/or FORMAT can be viewed as a table lookupallowing 1-to-1 mapping or many-to-1 mapping. Anexample of a 1-to-1 mapping is the mapping of ap-prove/decline codes used to process credit applica-tions. In the consumer credit card industry, applica-tion processing decisions are often abbreviated. Asimple example is; a = Approve d = Decline If we have many approval codes and many declinecodes, these can be mapped or assigned with amany-to-1 mapping.

3 For example; a1 , a2 , a4 = Approve d1 , d6 = Decline PROC FORMAT will not allow 1-to-many or many-to-many mappings. This is a good feature of PROCFORMAT since we don t want data values to take onmore than one label. When using a DATASTEP andIF-THEN-ELSE logic to assign labels, the SAS LOGwill not indicate if you have data values pointing tomore than one label. using PROC FORMAT insteadof IF-THEN-ELSE code will provide an extra qualitycontrol check of your assignments of labels to s look at a few sample problems and see howPROC FORMAT can be used to generate more effi-cient code.

4 These examples come from the consumercredit card industry but the concepts have applicationto many APPLICATION THAT ASSIGNS VALUES WITH-OUT using PROC FORMATWhen credit bureau data on individuals are re-quested, a generic credit bureau score can also bepurchased. This score ranks predicted risk for theindividual with higher scores being less risky thanlower scores. One such score has integer valuesfrom 370 to 870 with missing scores assigned to val-ues outside this wish to run a frequency distribution on individualswith scores less than 671 and those above 670.

5 Abeginning programmer would often handle this bycreating another DATASET where a new variable isgenerated to assign membership into low scores,high scores and missing groups. A PROC FREQ isthen submitted to get the desired frequency stuff; set cb; if 370<= score <= 670 then group= 670- ; else if 670 < score <= 870 then group= 671+ ; else group= unscored ;proc freq data=stuff; tables group; run;The results from the above code are are shown inFigure on the following SAS code above did the job, but it required that anew DATA-Step be created and the label unscored was truncated to unsc.

6 THE SAME VALUE ASSIGNMENT PROBLEMSOLVED using PROC FORMATAn alternative approach entails using a user-definedSAS FORMAT . This saves some processing time andresources. Below (next page) is the SAS Code withTHE POWER OF PROC FORMATJ onas V. Bilenas, Chase Manhattan Bank, New York, NYthe solution to same problem but using example here is of the simple value replace-ment variety of SAS FORMAT . We will discuss how toset up a FORMAT that assigns values to ranges in asubsequent FORMAT ; value score 370 - 670 = 670- 671 - 870 = 671+ other = unscored ;proc freq data=cb; tables score; FORMAT score score.

7 ;run;The SAS Code returns the output shown in :Some observations about this result are:1. The name of the FORMAT does not have to be thename of the variable that it will be assigned The assignment of the FORMAT occurs in thePROC with a FORMAT The FORMAT definition ends with the ; on a newline. This is just my preference but I find it eas-ier to debug PROC FORMAT code especially if Iadd more values to the FORMAT later The unscored label now appears without trun-cation. When assigning a character label in adataset, the length of the first evaluation of thelabel will be applied to all PROC FORMAT TO FIND UNEXPECTEDVALUESUser defined formats can be used to list out unex-pected values.

8 If a range of values are not mapped ina PROC FORMAT , the labels will be the original val-ues. Here is an example:proc FORMAT ; value looky 370-870 = 370-870 ;proc freq data=cb; tables score; FORMAT score looky.;run;The output of this code is shown in Figure below:Figure CumulativeGROUP Frequency Percent Frequency Percent--------------------------------- ------------------671+ 623 623 5170 5793 5 5798 CumulativeSCORE Frequency Percent Frequency Percent--------------------------------- ---------------------670- 5170 5170 + 623 5793 5 5798 * * * * * * * * * * * * * * * * *Figure CumulativeSCORE

9 Frequency Percent Frequency Percent--------------------------------- ---------------------370-870 30320 30320 9003 1264 31584 NEW VARIABLES WITH PROCFORMAT AND A VALUE REPLACEMENT FOR-MATNew variables can be assigned within a DATA-Stepusing user defined FORMATS. A nice feature ofusing FORMATS for generating new variables is thatthe method can replace IF/THEN/ELSE code. Inthis example we wish to assign expected delin-quency rates for given score ranges for a givenportfolio.

10 Proc FORMAT ; value edr low-159 = 160-169 = 170-179 = 180-high = ;data stuff; set cb2; edr=input(put(score,edr.), );run;With the above code a new variable called edr isgenerated from the call of the FORMAT in the PUTfunction. PUT always return a character, so the IN-PUT function was used to convert the variable tonumeric since we required that the edr variable be anumeric PROC FORMAT TO EXTRACT DATAUser defined formats can be used to extract a sub-set of data from a larger DATASET.