Handy-Dandy SAS® Functions, Part 3 of 3: Arrays with Do Over Loops

This Tech Tip was provided by Onecia Gibson, Research and Analysis Director Assistant for the Office of Institutional Research, University of Kentucky. 

This third part of the three-part series on Handy-Dandy SAS® Functions presents arrays with do over loops. SAS® 9.3 Language Reference: Concepts1 defines an array as a grouping of variables that are arranged in a particular order and identified by an array name. Arrays with do over loops are useful when the same set of statements needs to be applied to several variables in a database.2  

For example, in the database below, the values for LastName, FirstName, and CITY have been entered in uppercase letters. Suppose there is a need to have the values in proper case (first letter uppercase and following letters in lowercase).  

SAS 1 rev.PNG
Examples 1 (top) and 2

The SAS® code in example 1 applies the propcase function to each variable in separate statements. A more efficient method is illustrated in example 2 where an array is used with a do over loop. The name of the array is RESET and it contains the variables LastName, FirstName, and CITY. (The illustrated array contains only three variables, however, arrays can contain hundreds of variables.) When a do over loop is applied to the array, all statements between the do over statement and end statement are applied to each variable in the array. Therefore, in example 2, the propcase function is applied to each variable in the array. The output for both example 1 and example 2 is the following database.  

SAS 2 rev.PNG
Similarly, arrays can contain numeric variables. Several times, I have worked with databases in which null (missing) values are coded as 0, 98, or 99. Because SAS® uses the period (.) as a missing value for numeric variables, SAS® will treat 0, 98, and 99 as literal numeric values when any calculation is performed. For example, the database below uses 99 to represent missing values for Age and ACT score. If the average ACT score is calculated (e.g., using proc means), SAS® will return an average ACT score of 54.3. SAS 3 rev.PNG
Example 3 illustrates how an array with a do over loop efficiently replaces the 99 values with a period. After the replacements, SAS® proc means will return an average ACT score of 32.
SAS 4 rev.PNG


1 Base SAS® 9.3 Language Reference: Concepts, (February 2012).

2 Paper 158-2010: How to Use ARRAYs and DO Loops: Do I DO OVER or Do I DO i? (2010). Retrieved August 2, 2012.  



To add a comment, Sign In
Total Comments: 9
Erin posted on 8/23/2012 11:45 AM
Appears to be a very useful tip for SAS users. I would love to see something similar for SPSS.
Lucy posted on 8/23/2012 11:47 AM
If you ever need to change text to proper case in Excel you can do it with the PROPER function. =PROPER(A1) will return the text of A1 with the first letter of each word in capital and the remaining letters in lower case. And if you are working with a Microsoft Word document you can highlight the text and press <SHIFT>+<F3> to toggle through lower case, proper case and all capitals.
John posted on 8/23/2012 11:54 AM
Is there a way to loop over all the columns without having to type them in? In example 2 above, the statement "array RESET LastName, FirstName, CITY;" requires that I know the names of the columns ahead of time. Is there something like "array RESET *" or something else to pick all the column names?
Onecia posted on 8/23/2012 12:14 PM
John, You can define an array to include all character (text) variables by using the statement array RESET _character_. So, in the above example2 program, array RESET _character_ can be used in place of array RESET LastName FirstName CITY. Same idea for numeric variables, use array RESET _numeric_ instead of array RESET_N Age ACT.
Shelia posted on 8/23/2012 1:25 PM
It's been awhile for me with SAS, as I'm now hooked on SPSS. I do like the idea of using a period (.) since zero (0) is sometimes used for various responses, e.g., Not applicable.
Bamby posted on 8/23/2012 2:22 PM
I’m not an SAS user, but the directions and screen shots are clear and the information looks useful in terms of improving the appearance of SAS output.
Sri posted on 8/23/2012 2:24 PM
Good tips for SAS users.
Gary posted on 8/31/2012 1:26 PM
Thanks for the great SAS tips, Onecia. propcase is a nice convenience function.

Erin, I think you'd want something along these lines in SPSS.
This just converts to lower case to show the logic.
define myMacro (colNames = !charend('/'))
!do !i !in (!colNames)
compute !i = lower(!i).

myMacro colNames = Last_Name First_Name /.

...its nicer with R - no macros, no quirky limitations:
# ------------------------------------------------------------------------
x <- c("last.name", "first.name")
for (i in x) substr(ex.1[, i], 2, 99) <- tolower(substr(ex.1[, i], 2, 99))
# ------------------------------------------------------------------------
Matthew posted on 5/3/2017 12:00 PM
THank you! As a former STATA user that now uses SAS I have been trying to figure out how to do this for a while. Does anyone know if you can do this in a macro to loop over several variables through a proc? for example

do over VARLIST progress pretest posttest;
proc freq data=data;
table VARLIST;
where VARLIST ne 99;

This is very easy in STATA using a foreach loop, but I have never been able to figure out how to do it in SAS.