Index, Indexc and Indexw functions

Prepared by Meena R S

The INDEX, INDEXC and INDEXW functions are used for determining the character matches. These functions are useful for testing purposes. The index and indexc functions can indicate if a string of characters is present in a target variable. Both functions return the position number of the match in the target variable. A zero indicates that the search argument is not present in the target variable.

For finding a special character such as a letter, a group of letters, or special characters, Index functions can be used and it is case sensitive. The syntax is,

INDEX (source, excerpt)

The INDEXC function allows multiple arguments and will identify the first occurrence of any of the characters in any of the arguments, but otherwise functions similarly to the index function.

/* Example: 1 */

/* Results*/

The INDEXW function searches source, from left to right, for the first occurrence of excerpt and returns the position in source of the substring's first character. If the substring is not found in source, INDEXW returns a value of 0. If there are multiple occurrences of the string, INDEXW returns only the position of the first occurrence.

The INDEXW function is case sensitive function that performs exactly the same function as the INDEX function, with one significant exception. The indexw function searches for strings that are words, whereas the index function searches for patterns as separate words or as parts of other words.

/* Example: 2 */

/* Results */

Result Explanation

The above program demonstrates the difference between INDEX and INDEXW functions. In the first observation in the table above, the INDEX function returns a 1 because the letters "the" as part of the word "there" begin the string. Since the INDEXW function needs either white space at the beginning or end of a string to delimit a word, it returns a 12, the position of the word "the" in the string. Observation 3 emphasizes the fact that a punctuation mark does not serve as a word separator. Finally, since the string "the" does not appear anywhere in the fourth observation, both functions return a 0.

Read from multiple external files in one data step by using FILEVAR= option

Prepared by Jose Abraham

External files are usually read into SAS one by one using separate data steps for each external file. But multiple external files which have the same structure can be easily read into SAS in one data step by using FILEVAR= and END= options in the INFILE statement. Following example illustrates how to read multiple external files where the locations of the external files are stored in another external file.

Consider we have demographic information of subjects from three different centers stored in three external files. All the three external files have the same structure as given below




The data values are aligned in columns and there are no missing values. The layout follows.


We have another external file which contains the location information of these external files. Suppose these files are stored in the 'demog' folder in the E-drive, and the following external file (dmgfiles) which contains the locations is also in it.


Following SAS data step reads the three external files in one DATA step by using the names which are specified in the external file 'dmgfiles'. This reads the list to determine the external files it should read.



Data step working:

1.First INFILE statement specifies the name of the external file containing the list of filenames that the DATA step should read.

2.First INPUT statement reads the name of the external files with modified list input. A width (60) which is sufficient to hold the name of the external file is specified.

3.Second INFILE statement specifies a text, dummy, and this act as a placeholder for the file specification which is always required on the INFILE statement. The actual specification for the input file comes from the value of the variable assigned by the FILEVAR= option.

a.The FILEVAR= option is set to 'dmgfiles', the variable that contains the name of the external file that the current iteration of the data step should read.

b.END= option defines a variable that SAS sets to 1 when it reads the last data line in the currently opened external file. The END= variable is initialized to 0 and retains the value until it detects that the current input data line is the last in the external file. SAS then sets the variable to 1.

c.When the FILEVAR= option is included in the INFILE statement, SAS resets the END= variable to 0 when the value of the FILEVAR= variable changes (If SAS did not reset the value of the END= variable to 0 each time it opened a new external file, the DATA step would stop after reading the first external file).

4.The do while loop is controlled by testing the value of the END= variable. The loop stops after SAS reads the last data line in the currently opened external file.

5.Name of the file from which the records are read (source file name) is assigned into a variable 'Source'.

6.The above data step iterates four times: one for each of the dmg files (dmg01, dmg02, dmg03) and a fourth time in which it detects that there are no more data lines in the external file that contains the filenames.

7.The default behavior of SAS is that it writes an observation to a data set only at the end of each iteration of the DATA step. An explicit OUTPUT statement is specified to avoid this and output all data values read form the external file.

8.The output dataset 'demogdat' obtained is as follows

Source	Ctrn	Subjid	Age	Sex	Race
E:\demog\dmg01.txt	001	001_01	29	Male	Caucasian
E:\demog\dmg01.txt	001	001_02	28	Female	Caucasian
E:\demog\dmg01.txt	001	001_03	25	Male	Caucasian
E:\demog\dmg02.txt	002	002_01	27	Male	Asian
E:\demog\dmg02.txt	002	002_02	28	Male	Asian
E:\demog\dmg02.txt	002	002_03	25	Female	Asian
E:\demog\dmg03.txt	003	003_01	27	Female	Asian
E:\demog\dmg03.txt	003	003_02	28	Male	Asian
E:\demog\dmg03.txt	003	003_03	25	Female	Asian

Macro Variable Resolution Using Multiple Ampersands

Prepared by Meena R S (meena@kreara.com)

The SAS macro consists of two basic parts: macros and macro variables. The names of macro variables are prefixed with an ampersand (&) while the names of macros are prefixed with percent sign (%). Prior to the execution of the SAS code the macro variables are resolved. The resolved values are then substituted back into the code.

The macro variable references that have been described with one ampersand preceding the macro variable name are direct reference to a macro variable. In indirect referencing, more than one ampersand precedes a macro variable reference. The macro processor follows specific rules in resolving references with multiple ampersands.

The rules that the macro processor uses to resolve macro variable reference that contain multiple ampersands follow

• Macro variable references are resolved from left to right
• Two ampersands (&&) resolve to one ampersand (&)
• Multiple leading ampersands cause the macro processor to rescan the reference until no more ampersands can be resolved.

Consider the example below.

Options symbolgen;

%let section4 =operating system;

%let n=4;

%put &&section&n;

For the above code, on the first pass the two ampersands are resolved to one and &n is resolved to 4, yielding &section4. On the second pass the macro variable reference &section4 resolves to operating system.

The following figure shows the process of resolving the macro variable reference in the program.

%let a =freight;

%let b=passenger;

%let c=special;

%let code=a;

%put &code;

%put &&code;

%put &&&code;

The following demonstrates how the macro variables with multiple ampersands are resolved.

Combining SAS data sets using UPDATE statement

Prepared by Rajeev V (rajeev@kreara.com)

UPDATE is an executable type SAS statement generally used in DATA steps. By updating a SAS dataset replaces the values of variables in one dataset with values from another dataset. The dataset containing the original information is the master data set, and the data set containing the new information is the transaction data set. UPDATE performs much the same function as merge with two exceptions:

Ø Only two datasets can be combined using UPDATE statement

Ø If the observation of a variable in the transaction dataset (i.e. data set containing new information) is missing, then the updated dataset containing the value of the observation same as in the master dataset

Syntax

DATA updated data set;

UPDATE master-data-set transaction-data-set;

BY variable-list;

RUN;

Where Master-data-set names the SAS data set that is used as the master file. Transaction-data-set names the SAS dataset that contains the changes to be applied to the master data set. Variable-list specifies the variables by which observations are matched.

Basic use of UPDATE statement

Consider two datasets, Lab1 and Lab2. Both data sets have four subjects with information about their blood pressure at two different times. The first dataset Lab1 contains the subject id, name of the subject and Blood pressure reading. The second dataset contain the latest blood pressure reading for the same subjects but the name of the subjects is not given.

It is required to replace the latest Blood pressure values in LAB1 with the values in LAB2. Here LAB1 is known as Master data set and LAB2 is transaction dataset.

Lab 1

Subject Name BP

001 AAA 120

002 BBB 130

003 CCC 140

004 DDD 150

Lab 2

Subject BP

001 160

002 160

003 160

004 160

The following program updates LAB1 (Master Dataset) with LAB2 (Transaction dataset).

DATA lab1_updated;

UPDATE lab1 lab2;

BY Subject;

RUN;

Printed output of lab1_updated is given below;

In the above example it is found that the latest blood pressure value is populated into updated dataset based on the BY variable Subject identifier.

Suppose Lab1 contains a duplicate observation. For example,

Lab 1

Subject Name BP

001 AAA 120

002 BBB 130

002 BBB 130

003 CCC 140

004 DDD 150

DATA lab1_updated;

UPDATE lab1 lab2;

BY Subject;

RUN;

If the above program is executed updating will not work for duplicate BY value.

That means if master data set contains two observations with the same value of the BY variable, the first observation is updated and the second observation is ignored. SAS writes a warning message to the log when the data step executes.

Printed output of lab1_updated is given below;

Observations two and three have duplicate values of BY variable subject identifier. However, the value of variable Blood pressure was not updated in the second occurrence of the duplicate BY value.

A situation may arise where a missing observation can occur in master dataset or in transaction dataset. Below example illustrates updating when dataset contains unmatched and missing observations.

Lab A

Subject Name BP

001 AAA .

002 BBB 130

003 CCC 140

004 DDD 150

Lab B

Subject BP

001 160

002 .

003 160

004 160

In the above dataset Lab A (Master Dataset) contains missing value corresponding to the subject 001 and the Lab B (Transaction dataset) contains missing value corresponding to the subject 002. The output of the below code gives the latest known BP value for each subject.

DATA lab1_updated;

UPDATE LabA LabB;

BY Subject;

RUN;

Printed output is given below;

The following is a more advanced example where the UPDATE statement is used to “flatten” a dataset containing different variable data values for a key spread across several observations. The goal is to combine non-missing values into one record per unique key value. The master dataset structure is read, but the OBS=0 option stops the DATA step from reading any data from it. The same dataset is then uploaded as transaction dataset, flattening non-missing observations for each unique key into one observation.

DATA lab;

INPUT Subject $ Calcium Albumin Chloride;

CARDS;

001 2.25 . .

001 . 49 .

001 . . 100

002 3.1 . .

002 . 50 90

002 . . .

;

RUN;

DATA lab;

UPDATE lab (obs=0) lab;

BY subject;

RUN;

Output is given below;

INFILE OPTIONS

Prepared by Sreeja E V(sreeja@kreara.com)

Infile has a number of options available.

FLOWOVER

FLOWOVER is the default option on INFILE statement. Here, when the INPUT statement reaches the end of non-blank characters without having filled all variables, a new line is read into the Input Buffer and INPUT attempts to fill the rest of the variables starting from column one. The next time an INPUT statement is executed, a new line is brought into the Input Buffer.

Consider the following text file containing three variables id, type and amount.

11101 A

11102 A 100

11103 B 43

11104 C

11105 C 67

The following SAS code uses the flowover option which reads the next non missing values for missing variables.

data B;

infile "External file" flowover;

input id $ type $ amount;

run;

which creates the following dataset

MISSOVER

When INPUT reads a short line, MISSOVER option on INFILE statement does not allow it to move to the next line. MISSOVER option sets all the variables without values to missing.

data B;

infile "External file" missover;

input id $ type $ amount;

run;

which creates the following dataset

TRUNCOVER

Causes the INPUT statement to read variable-length records where some records are shorter than the INPUT statement expects. Variables which are not assigned values are set to missing.

Difference between TRUNCOVER and MISSOVER

Both will assign missing values to variables if the data line ends before the variable’s field starts. But when the data line ends in the middle of a variable field, TRUNCOVER will take as much as is there, whereas MISSOVER will assign the variable a missing value.

Consider the text file below containing a character variable chr.

a

bb

ccc

dddd

eeeee

ffffff

Consider the following SAS code

data trun;

infile "External file" truncover;

input chr $3. ;

run;

When using truncover option we get the following dataset

data miss;

infile ""External file" missover;

input chr $3. ;

run;

While using missover option we get the output