6781097195

Function	Use
	or stop when sub stringing a variable.
Left	Left justifies the variable value.
Length	Returns the number of characters with a character variable value.
Lowcase	Lower cases all letters within a variable value.
Right	Riqht justifies the variable value.
Scan	Returns a portion of the variable value as defined by a delimiter. For example, the delimiter could be a space. comma, semi-colon etc.
Substr	Returns a portion of the variable value based on a starting position and number of characters.
Translate	Replaces a specific character with characters that are specified.
Tranwrd	Replaces a portion of the character string (word) with another character string or word. For example, a delimiter was supposed to be a comma but data in some cases contains a colon. This function could be used to replace the comma with a colon.
Trim	Removes the trailing blanks from the riqht-hand side of a variable value.
Upcase	Upper cases all letters within a variable value.

If you need to use one of these functions on a numeric variable then it is preferable to first convert the numeric value into a character value (see previous section). By default, conversion from numeric to character will occur when using these functions within the DATA step with a warning placed at the end of the DATA step.

For example -

A new mailing list contains a datę value that is a character and it needs to be converted into a SAS datę value. An additional challenge is that the character value does not match any datę informats.

data newlist; set newdata.maillist;

/* Extract month, day and year */ /* from the datę character var' */

» = scan(date,1,’ ■); d = scan(date,2,’ '); y = scan(year,2,’,; dd = compress(d||m||y,’

/* Convert mon, day, year into */ /* new datę variable^b    */

newdate = input(dd,date9.);

a)    In this case the SCAN function was used, but the SUBSTR function could also have been used to extract the month, day, and year from the original character datę variable. The SCAN function was used because the data values contained a space or comma delimiter. Notę that the comma was used to delimit the year and the text portion was the second and NOT the third. The reason for this is the text string has only two pieces, month and day, before the comma and year after the comma, when the comma is used as the only delimiter. The SUBSTR function would have been the only choice if a delimiter had not been available.

b)    Conversion of the resulting mon, day and year variables into a new variable was accomplished by utilizing the COMPRESS function and INPUT functions. The COMPRESS function was used to remove any spaces present within the three (3) concatenated variables and to remove the comma within the day variable value. Notę - by choosing to use the scan function for extracting the day value from the original datę variable, the comma was left with the day value sińce there was no space between the day and comma. Finally, the use of the INPUT function creates a new variable with a SAS datę value.

Datę character value format - Mon dd, yyyy    Parsing along -

The solution to this conversion has two (2) steps -

1.    Need to re-arrange the datę character value so that the datę is in the following format -ddmonyyyy, i.e. date9. informat.

2.    Convert the new character value to a datę

In many data cleansing scenarios, a single data variable contains multiple pieces of data that need to be split into separate variables. If there is no delimiter between them, then the variable must be divided using the SUBSTR (substring) function.

The SUBSTR function requires a starting point and the number of characters to be kept in the new variab!e. In some cases however, the starting point may not be constant. In those cases then several