SAS Programming Basics

SAS is a powerful and flexible statistical package that runs on many platforms, including Windows and Unix. This class is designed for anyone interested in learning how to write basic SAS programs. Some familiarity with SAS is recommended. If you are new to SAS you may want to review our Introduction to SAS Seminar.  It is expected that those attending this course have the ability to navigate to and access data files on their own operating system. The students in the class will have hands-on experience using SAS for data manipulation including use of arithmetic operators, conditional processing, using SAS built-in functions, merging, appending, formatting and different options for modifying SAS output. It is our hope that after this seminar you will be able to:

• Comfortably navigate the SAS window environment
• Subset and create new datasets
• Create new variables
• Write and debug basic SAS programs
• Use SAS function for basic data management tasks
• Merge and append data
• Modify SAS output for presentation

Please note that since we are using data files provided by SAS, we are unable to make these available on our website. Thus, this seminar page includes output from the SAS procedures used in the seminar.

For clarity all SAS keywords will be in CAPITAL letters in order to distinguish them from the information that you as the user will provide.

Note: This seminar was developed in SAS 9.4

1.0 SAS Refresher

1.1 Libname

We will start by setting our libname, which opens a directory to the location where our SAS data files are stored.

*assign libname
LIBNAME idre 'C:';

SAS also allows you to clear a particular libname or use the _all_ keyword to clear all assigned libnames.

*clear libname;
LIBNAME idre CLEAR;
LIBNAME _ALL_ CLEAR;

* reassign library;
LIBNAME idre 'C:';

1.1 SAS Windowing environment

Let’s briefly review the SAS windowing environment. The five main windows in SAS are the Explorer, Results, Program Editor, Log, and Output/Results Viewer windows. In general, when you start SAS, the windows that initially appear are the Log, Editor and Explorer windows. Other windows can be found under the View menu in the toolbar.

The SAS Explorer window allows you to manage files associated with your current SAS session including viewing, deleting, moving, and copying files. The Editor window, which is literally just a text editor, permits you to enter, edit, submit and save SAS programs. The Log window allows the user to view information about their current session including messages about submitted SAS programs such as successful execution, errors or warnings. The Results window enables you to view a list of results from executed SAS programs. The Results Viewer allows you to view HTML results of executed SAS procedures. In SAS 9.4, the default output format is HTML.

1.2 Creating new SAS datasets

As we will be using several different datasets in the seminar today, let’s also cover how to create new permanent and temporary datasets from the data files you have been provided.

*permanent dataset;
DATA idre.new;
SET idre.charities;
RUN;
*temporary dataset;
DATA new;
SET idre.charities;
RUN;

1.3 SAS Options

SAS includes a large suite of system options that will affect your SAS session. Specific options are invoked by default when you open SAS. The options can vary depending what computing environment you are using (e.g. Windows, Unix). The OPTIONS procedure lists the current settings of SAS system options in the SAS log.

PROC OPTIONS;
RUN;

SAS includes two types of options: portable and host.  Portable or nohost are options that are the same regardless of the operating system. Host options are different depending on which operating system you are using.

Below are some examples of common options and what they are responsible for doing.

The AUTOCORRECT option is turned on by default and allows SAS to correct syntax with small mistakes like a misspelled keyword. In the first example below, the DATA keyword is misspelled to DATE. When the option is invoked, you will see that in the Log (shown below), SAS issues a warning it assumed that the keyword was misspelled and continues executing the procedure. However, in the second example where the automatic correction option is turned off, SAS issues an error and stops executing the procedure.

*autocorrect option;
OPTIONS AUTOCORRECT; /*default*/
PROC FREQ DATE=idre.charities;
TABLE code;
RUN;

OPTIONS NOAUTOCORRECT; 
PROC FREQ DATE=idre.charities;
TABLE code;
RUN;

The FMTERR option controls whether SAS will issue a warning for incorrect formats being used for variables. In this case, the  default is for SAS is to error and stop processing the executed procedure. In the first example, the default option is invoked and as you can see below SAS issues a warning that the format used could not be found. However, in the second example where we tell SAS to not issue an error (NOFMTERR), SAS ignores the incorrectly used format and will the execute the command without the format.

*format error; 
OPTIONS FMTERR;/*default*/
PROC PRINT DATA=idre.charities;
FORMAT code $code.;
RUN;

OPTION NOFMTERR;
PROC PRINT DATA=idre.charities;
FORMAT code $code.;
RUN;

2.0 Diagnosing and Correcting Syntax Errors

A main issues with learning a new programming language is the ability to identify and address coding errors. There are several ways that SAS will notify you of syntax errors.

2.1 Color-Coded Syntax.

When executing code in SAS in the Enhanced Editor you will notice some color coding. Color coding program components will help you more easily diagnose syntax errors, and when you first start with SAS you will make many mistakes. Take a look at the example syntax below copied from the Enhanced Editor window. Here you will see 5 different colors automatically generated by SAS. For example you will see that keywords like DATA, CLASS, MODEL are all highlighted in blue. If you use the wrong keyword with a procedure, the keyword will often remain black like the variable names because SAS does not recognize it. Options like SOLUTION are also considered keywords. As we will discuss later, the way to indicate a format is to put a period at then end and, once you do this, it will turn green. Anything in quotation marks turns red. In the second set of code, you will see that we are missing an end quote, thus all of the syntax is red. Thus we would know to correct the missing double quote.

2.2 Log File

The log file will also let you know when you have syntax errors. Below is an example using PROC MEANS:

In the syntax shown, we are attempting to run the MEANS procedure with a couple options. We have added “average” and “min” options to our statement to indicate that we only want to the average and the minimum values for salary. As we described in the previous section, options should be colored in blue and in this example “average” remains black indicating the SAS is not recognizing it as a keyword.

Below we see what happens when we attempt to execute the syntax as written. An error appears in the log file indicating that the keyword “average” was not a recognized option. Additionally, in this instance, SAS provides a list of alternate options you may have wanted. If you look carefully, you will see that “mean” is one of them. If we replace the unrecognized keyword “average” with “mean” the procedure will execute as expected.

3.0 Data Step vs. Proc Step

SAS programs are comprised of two distinct steps: data steps and proc steps. Data steps are written by you, while procedures are pre-written programs that are built-in. In general, Data steps are used to read, modify and create data files and always begin with a “DATA” statement. You saw an example of a data step in section 1.2. From a statistical standpoint a Proc step is typically used to analyze a dataset in SAS without making changes to the data.  There are exceptions to this. Proc steps always start with the familiar “PROC” statement. You have seen several examples of Proc steps in the preceding sections including PROC PRINT, PROC MEANS, and PROC FREQ. Each procedure enables us to analyze and process that data in specific way.

In the following sections we will demonstrate how to use these two types of steps.

4.0 Manipulating Datasets

4.1 Operators

An operator in SAS is a symbol representing a comparison, logical operation or mathematical function.

4.1.1  Comparison Operator

These are operators that compare a variable with some specified value or another variable. They are typically represented as symbols such as =, <, > but also have mnemonic equivalents like EQ, LT, or GT, respectively. The operators can used within a data or proc step depending on your needs.

One of the simplest ways to use a comparison operator is in a WHERE statement. In the “sales” data file, we have information on sales associates from Australia (AU) and the United States (US). If we only wanted to output records for Australian sales associates we could use the = or eq operator. Since the variable country contains character information not numeric, we need to put single quotes around ‘AU’.

PROC PRINT DATA=idre.sales;
WHERE Country='AU';
RUN;

The IN operator can be used if you are trying to specify a list or range of values, as demonstrated below.

PROC PRINT DATA=idre.sales;
WHERE Country IN ('AU', 'US');
RUN;

We also can specify SAS to output only certain ranges of values for numeric variables. In the first example below we ask SAS to output salary values that are less than (<) $30,000. In the second example, we output salary values greater than or equal to (ge) $30,000.

PROC PRINT DATA=idre.sales;
WHERE Salary<30000;
RUN;

PROC PRINT DATA=idre.sales;
WHERE Salary ge 30000;
RUN;

One limitation of using a WHERE statement is that more than 1 can not be used simultaneously, except in special cases. If you attempt to submit the following syntax, SAS will issue a note in the Log stating “WHERE clause has been replaced.” It will then execute the following syntax omitting the first WHERE statement. However, in the next section we will demonstrate how to combine comparison operators with logical operators to achieve the desired output.

PROC PRINT DATA=idre.sales;
WHERE Country='AU';
WHERE Salary<30000;
RUN;

For more example checkout SAS 9.4 Help and Documentation page on comparison operators.

4.1.2 Logical Operators

The logical or Boolean operators include AND, OR, & NOT. They are often used to link a series of comparisons. Just like the comparison operators, these can be written as either symbols or mnemonics. Below is table showing the each symbol and it’s mnemonic alternative.

In the previous section we learned that we cannot use two WHERE statements, but we can use the AND operator to combine the information contained in those two statements to achieve the desired result.

Below we use AND to output observations representing Australian sales associates that make less than $30,000 a year. Both the symbol and mnemonic are used and they give the same result.

PROC PRINT DATA=idre.sales;
WHERE Country='AU' AND Salary<30000;
RUN;

PROC PRINT data=idre.sales;
WHERE Country='AU' & Salary<30000;
RUN;

As with comparison operators you can also combine  AND, OR, & NOT with the IN operator. In the example below the variable job_title includes seven different job types. We want to obtain frequencies of all of them except for two, Sales Manager and Sales Rep IV. So we use the NOT combined with IN since we have more then one value we are trying to exclude.

PROC FREQ DATA=idre.sales;
TABLES Job_Title;
WHERE Job_Title NOT IN ('Sales Manager','Sales Rep. IV');
RUN;

For more example checkout SAS 9.4 Help and Documentation page on logical operators.

4.1.3 Where Operators

We have just covered several examples using the comparison and logical operators. However, SAS does include a set of special operators that can be used only in WHERE expressions. Some of these have similar functions to comparison operators.

For example, here are three ways of specifying that we want SAS to output all sales associate records with salaries that range from $28,000 to $30,000. As in any good programming language, there are always multiple ways of doing the same thing.

* We can use only comparison operators;
PROC PRINT DATA=idre.sales;
WHERE 28000<=Salary<=30000;
RUN;

*We can use a mix of comparison and logical operators;
PROC PRINT DATA=idre.sales;
WHERE Salary>=28000 & Salary<=30000;
RUN;

*We can use only the special WHERE operators;
PROC PRINT DATA=idre.sales;
WHERE Salary BETWEEN 28000 AND 30000;
RUN;

Earlier, we discussed that, in general, SAS does not allow you to use more then one WHERE statement in the same data or proc step. The exception to this are the special operators “same-and” and “also”. These will allow you to update or augment an existing WHERE statement to add an additional conditions. In the example below the first condition subsets the data to Australian sales associates that make less then $26,000, and then we add the additional clause that they must also be female.

*Using Same and; 
PROC PRINT DATA=idre.sales;
WHERE Country='AU' and Salary<26000;
WHERE SAME AND Gender='F';
VAR First_Name Last_Name Gender Salary Country;
RUN;

*Using Also;
PROC PRINT DATA=idre.sales;
WHERE Country='AU' & Salary<26000;
WHERE ALSO Gender='F';
VAR First_Name Last_Name Gender Salary Country;
RUN;

Now while some of these special operators are fairly self-explanatory like “Is Null” some may be less so, such as “=*” and “Like”. These operators can be helpful for identifying issues such as misspelled information, incorrectly entered information, or identifying related names or titles that vary. For example, below is a dataset called “shoes_eclipse” that includes several different product names:

Let’s suppose we are interested in identifying product names that include the word “Woman’s”. How could we do that? The “Like” operator could help us do this. It works by comparing character values to some given pattern. It requires two special characters, a percent (%) sign and an underscore (_). The percent denotes that any number of characters may occupy a position. However, the underscore specifies that only one character may occupy a position. If we are only interested in products that start with “Woman’s”, then we don’t care how many spaces come after “Woman’s”:

PROC PRINT DATA=idre.shoes_eclipse;
VAR product_name;
WHERE product_name LIKE "Woman's %";
RUN;

I also could ask SAS to output to me any name that includes “Men’s” any where in the title. This would require multiple % signs b/c any product name with “Men’s” may have character spaces before and after.

PROC PRINT DATA=idre.shoes_eclipse;
VAR product_name;
WHERE product_name LIKE "% Men's %";
RUN;

For more information check out SAS Help and Documentation on special WHERE operators.

4.1.4 Arithmetic operators

Arithmetic operators, as you can probably tell from the name, allow you to perform arithmetic calculations in SAS.  Below is a table of the operators and their symbols used in SAS.

A few things to note about using these operators. First, if you are calculating values using a variable(s) with missing data, the resulting value will also be missing. Second, expressions are evaluated with respect to the traditional order of operations with exponentiation taking the highest priority level, then multiplication/division and last addition/subtraction. This ordering can be modified by using parentheses.  Third, as is the case with the other operators we have discussed, arithmetic operators can be using on conjunction with both logical and comparison operators.

Let’s try a few examples. Below we will use a Data step to create a new temporary dataset called “sales_subset” from the “sales” data. This dataset will contain only observations from Australian employees whose job title contains the word “Rep”.  So we are using a logical and special WHERE operator. Additionally, we are creating a new variable called “Bonus” which is calculated by multiplying “Salary” by .10.

DATA sales_subset;
SET idre.sales;
WHERE Country='AU' & Job_Title contains 'Rep';
Bonus=Salary*.10;
RUN;

Below we output the first 20 records of our new dataset.

In this second example let’s use parentheses to change the estimation of a compound (more then one operator) expression. We will use a Data step to create two new variables profit1 and profit2.

DATA profit;
SET idre.order_fact;
profit1 = total_retail_price - costPrice_per_unit * quantity;
profit2 = (total_retail_price - costPrice_per_unit) * quantity;
RUN;

Let’s see how the use of parentheses has changed our values.

For more example checkout SAS 9.4 Help and Documentation page on arithmetic operators.

4.2 Conditional Processing

4.2.1 WHERE and IF statements

Conditional processing in SAS allows the user to manipulate and output portions of data instead of the whole file.  In previous section you have seen several examples of the WHERE statement. Alternatively, SAS also allows for the use of IF statements. Both can accomplish similar tasks; however ,while both WHERE and IF can be used with a Data step, only WHERE is allowed in a Proc step. For example, if we add an IF statement to the PROC MEANS command from earlier we will see that the IF turns red. This indicates that the syntax is incorrect.

However if you use WHERE the statement is blue.

If you attempt to execute the PROC MEANS using the incorrect IF statement SAS will produce an error but SAS will execute the command using the WHERE statement.

Data steps will accept both WHERE and IF statement, however only an IF can be used for assignment statements. Below is an example of an assignment statement. Assignment in this case means we are taking observations with values for salary that are greater than $30,000 and assigning them, using THEN OUTPUT, to a new dataset called “highsales”.

DATA highsales ;
SET idre.sales;
IF salary GT 30000 THEN OUTPUT highsales;
RUN;

You can also combine WHERE and IF in the same Data step as demonstrated below. We use an IF and WHERE statement to subset the data. Can you think of equivalent ways of subsetting the data?

DATA emps;
    SET idre.sales;
WHERE Country='AU';
Bonus=Salary*.10;
IF Bonus>=3000;
RUN;

Moreover, you will notice that SAS allows you to create a variable and use it in an IF statement in the same Data step.  This is something you can accomplish with an IF but not WHERE. The reason for this has to do with when SAS executes conditional statements. When using a WHERE condition SAS only selects the observations that meets this particular condition and then continues executing any other operations in the Data Step.  This makes for more efficient processing of data especially with large amounts of data. But in this instance if we had used a WHERE statement to subset the data using “Bonus”, SAS would have given us an error saying the “Bonus” variable is not in the dataset. However, IF conditions are not processed until the end of the Data step. Thus, SAS will execute the WHERE statement and create “Bonus” and then assess whether the IF condition is true.

4.2.2  If-Then statement

An If-then statement is a commonly used assignment statement that is typically carried out within the context of Data Step. It executes a SAS statement that fulfills a certain condition.

We will once again create a variable called “Bonus”, but assign the values based on a certain set of conditions that are defined by an employee’s job title.

DATA comp1;
SET idre.sales;
IF Job_Title='Sales Rep. IV' THEN Bonus=1000;
IF Job_Title='Sales Manager' THEN Bonus=1500;
IF Job_Title='Senior Sales Manager' THEN Bonus=2000;
IF Job_Title='Chief Sales Officer' THEN Bonus=2500;
RUN;

You will see in the output above, that several observations have missing values. This is due to the fact that we did not assign values for “Bonus” for all of the job titles.

A related statement to IF-THEN is the ELSE statement that can be used when creating conditional statements around mutually exclusive groups.

DATA comp2;
SET idre.sales;
IF Job_Title='Sales Rep. IV' THEN Bonus=1000;
ELSE IF Job_Title='Sales Manager' THEN Bonus=1500;
ELSE IF Job_Title='Senior Sales Manager' THEN Bonus=2000;
ELSE IF Job_Title='Chief Sales Officer' THEN Bonus=2500;
RUN;

SAS process the first IF statement and if it is not true it moves to the next and so on. SAS continues to test the IF-THEN statement until it finds one that is true, which at that point it stops and will not test the remaining conditions. Once again, this can speed up the processing of large datasets. However, as was the case with the first IF-THEN example, we will end up with a lot of missing values using this syntax.

What if we had a scenario where we wanted to give all the remaining categories, that did not fulfill the prescribed conditions, one bonus value. We can do that using a final ELSE statement with no IF-THEN. In the SAS code below, we add an additional ELSE statement assigning all of the job titles a bonus value of 500.

DATA comp3;
SET idre.sales;
IF Job_Title='Sales Rep. III' or Job_Title='Sales Rep. IV' THEN Bonus=1000;
ELSE IF Job_Title='Sales Manager' THEN Bonus=1500;
ELSE IF Job_Title='Senior Sales Manager' THEN Bonus=2000;
ELSE IF Job_Title='Chief Sales Officer' THEN Bonus=2500;
ELSE Bonus=500;
RUN;

Now, we have complete data for all observations.

A second related statement to IF-THEN is the DELETE statement. In all of the previous examples we have used the IF-THEN statement to add information but you can also use the IF-THEN to delete as well. Using the IF-THEN-DELETE  syntax we can specify that certain observations fitting our condition be permanently deleted from the data. In the example below, we delete all observations associated with three specific job titles.

DATA drop;
SET idre.sales;
IF Job_Title IN('Sales Manager', 'Senior Sales Manager', 'Chief Sales Officer') THEN DELETE;
RUN;

4.2.3   Using Do

Typically with an IF-THEN statement only one executable statement is allowed. When an expression is true the associated statement is executed. But what happens if you want more then one statement executed for each expression. For example, let’s imagine that for each bonus value, I also want to create a variable called freq that denotes how many times a year the sales associate can receive the bonus (e.g. once a year, twice a year). So we might try the following code using a logical operator.

DATA freq1;
SET idre.sales;
IF Job_Title='Sales Rep. III' or Job_Title='Sales Rep. IV' THEN Bonus=1000 & Freq = "once a year";
ELSE Bonus=500 & Freq = "twice a year";
RUN;

While this syntax appears reasonable, SAS will execute the statement and the issue a note in the log that “Variable Freq is uninitialized”. When SAS is unable to locate a variable in a DATA step, SAS prints this message. If you look in the freq1 SAS dataset you will see that  SAS created the variable but sets all of it’s values to missing which is undesirable. It appears that creating “Freq” will require a separate statement instead of just a simple “&”. You could try this:

DATA freq2;
SET idre.sales;
IF Job_Title='Sales Rep. III' or Job_Title='Sales Rep. IV' THEN Bonus=1000;
ELSE Bonus=500;
IF Job_Title='Sales Rep. III' or Job_Title='Sales Rep. IV' THEN Freq = "once a year";
ELSE Freq = "twice a year";
RUN;

But this code could get fairly long if you have a lot of variables to create. A better way to do this would be through the use of a DO group which allows for multiple statements.

DATA bonus;
SET idre.sales;
IF Country='US' THEN DO;
Bonus=500;
Freq='Once a Year';
END; 

ELSE DO; Bonus=300; Freq=’Twice a Year’; END; RUN;

While the syntax looks similar to a traditional IF-THEN, there are some important differences. First, the IF expression now ends with THEN DO. This is followed by a set of statements to be executed. Second, each DO block ends with an END statement. Third, instead of just ELSE we now have ELSE DO which also has an END statement. If you are missing an END, SAS will issue a warning in the log and fail to execute the Data step.

4.3 SAS Functions

Functions accepts arguments and then produce a particular value (numeric or character) based on those arguments. Arguments are enclosed within parentheses and each argument is separated by a comma. SAS has a wide array of different functions depending on the needs of the user and can be used in Data step.  We will cover a few examples of basic mathematical functions, common date functions, and some additional functions useful for specific data management tasks.

4.3.1  Arithmetic Functions

In the first example, we will use the “Oldbudget” data file to calculate the total and average amount budgeted for business operations over a five year period.

DATA budget;
SET idre.oldbudget;
sum1 = yr2003 + yr2004 + yr2005 + yr2006 + yr2007;
sum2 = SUM(yr2003, yr2004, yr2005, yr2006, yr2007);
sum3 = SUM( of yr2003-yr2007);

mean1 = (yr2003 + yr2004 + yr2005 + yr2006 + yr2007)/5;
mean2 = MEAN(yr2003, yr2004, yr2005, yr2006, yr2007);
mean3 = MEAN( of yr2003-yr2007);

RUN;

There are many different way of creating the sum and mean variables that we need. We create “sum1” using an arithmetic operator to add the 5 budget amount together. Alternatively, we can use the SUM() function, the arguments are the variables you wish to sum together.  The difference between using the function versus manually adding together each variable is the treatment of missing. When we add items using “+”, a case with missing values on any of the variables listed will have a missing value for the resulting variable. If we use the SUM() function, any missing values will be treated as though they were zero, and the new variable will be equal to missing only if all of the variables listed are missing. Which method is most appropriate depends on the situation and what you are trying to achieve. Last, if you have a lot of variables to be summed you can specify a SAS variable list. This syntax works since the variable being specified are consecutive in the data. Check the SAS documentation page on SAS variable lists on how to use this shortcut in other circumstances. We also use similar syntax to demonstrate how to estimate the average or mean budget variables.

All the values produced for “sum1-sum3” and “mean1-mean3” are the same since we do not have any missing data.  SAS has a number of additional mathematical functions including absolute value, maximum, minimum and square root that can be used in a similar manner.

4.3.2 Date Functions

One of the more challenging data types to deal with in any data analysis package are date values. Thankfully, SAS has some built-in functions that can assist users with managing this data type. SAS stores date information as numeric values representing the number days before or after Jan 1, 1960. SAS can also recognize 2 or 4 digit year values. We will use the “Sales” dataset which includes information on date of birth and hiring data for each employee to demonstrate some date functions.

DATA comp;
SET idre.sales;
Hire_Month=MONTH(Hire_Date);
Birth_Day = WEEKDAY(Birth_date);
Day_Dif = DATDIF(Birth_date,Hire_Date, 'actual');
Month_dif= INTCK('years',Birth_date,Hire_Date); 
Bonus_1 = INTNX('month', Hire_Date, 6); 
RUN;

The MONTH function pulls the month from “Hire_date” and put’s it in a variable called “Hire_month”. The WEEKDAY function figures out what day of the week (1-7) the date would have fallen on and outputs this.

DATDIF calculates the difference in days between two dates given in the first two arguments. The third argument specifies the method for calculating the days. In this example we specify we want the ‘actual’ number of days, but we could choose other methods of calculation such assuming that each month has 30 days and that a year always has 360 days.

INTCK counts the number of intervals between two dates, in our example we asked SAS to output the number of years between an employees data of birth and when they were hired which we would be equivalent to an employees age at the time of hire.

INTNK  is used to estimate calculate the variable bonus_1. Here we want to calculate when an employee with be eligible for their next bonus. The arguments for this function are the unit of time, the variable representing the start date/time and the number of increments. In our example, employees are eligible 6 months after their hire date.

Below is the output of the first 10 observations of the “comp” dataset, with and without date formats. As mentioned before, SAS stores date information as numeric information in days. Thus if you do not format date with a format statement (discussed further in the next section), it will display as just a number.

PROC PRINT DATA=comp (OBS=10);
VAR Employee_ID Hire_date Hire_Month Birth_date Birth_Day Day_dif Month_dif Bonus_1;
*FORMAT Hire_date Birth_date Bonus_1 mmddyy10.;
RUN;

More example of SAS date function can be found on the SAS Help and Documentation website.

4.3.3  Other Functions

SAS includes several other types of functions designed for specific types of needs many of these functions are helpful for data management of character or string information. For example, LENGTH tells the user the length of a character string while COMPRESS will compress string values and remove unwanted blanks and specific character values like dashes. Additionally, in similar way to extracting date information like the MONTH function, SAS has several functions including SCAN and SUBSTR that allows you to extract words from a phrase.

Let’s demonstrate these. Below is sample of data from a data set called “Shoes_eclipse” where all the variables have character information. Our task of interest is to obtain the length of product_name, compress product_name to remove the blanks,  and create a variable the extracts the brand name “Eclipse” from product_group.

DATA shoes;
SET idre.shoes_eclipse;
length_name = LENGTH(product_name);
comp_product = COMPRESS(product_name);
brand = SUBSTR(product_group, 1, 7);
brand2 = SCAN(product_group, 1, " ");
RUN;

You will notice a few things about the output above. First, for the variable length_name, if you counted the number of letters and spaces in product_name you would end up with the same values displayed above.  Second, the compressed version of product_name now includes no spaces.  Third, both SCAN and SUBSTR functions produced the same output. The SUBSTR function takes 3 arguments, the name of variable with the information you want to extract, the character position you want to start from and then the number of character to extract. In our example we are telling SAS that we want to extract a character string of length 7 starting at the first character position of “product group” which would be the “E” in Eclipse. Unfortunately, this means whatever value we are extracting must always be of the same length. What if we have product names of different lengths. Then you might want to use the SCAN function, which work very similar to SUBSTR except, instead of specifying the length of the string, the last argument is a delimiter. The syntax above indicates that the character string of interest starts at the first position and continues until a blank/space is encountered.  This function works with many types of delimiters including < ( + & ! $ * ) ; ^ – / , % .

In the previous examples, we were extracting values from a string, but what if we wanted to combine string variables. A useful function would be CATX. Below we want SAS to combine the character string information in first_name and last_name into one fullname variable.  Additionally, the function also requires the specification of variables that includes information on the delimiter of choice. In the first example, the delimiter is just a blank while in the second example the delimiter is a comma.

DATA salesquiz;
SET idre.salesquiz;
sep = " ";
fullname = CATX(sep, first_name, last_name);
sep1 = ",";
fullname1 = CATX(sep1, last_name, first_name);
RUN;

The new variables are displayed above.

A list of all SAS functions, by category, can be found here on the SAS website.

Note: The order in which the variables are specified in the CATX function governs the order in which they will be combined.

4.4 Sorting, Merging and Appending

4.4.1 Sorting

The are many instances when having your data sorted in a particular way will be helpful for visualizing your data. Additionally, certain types of data management needs like merging datasets or grouping observations by a particular characteristic require sorting.

Sorting data by a single variable in SAS is the most simple. By default SAS sorts data ascending with the smaller values first.

PROC SORT DATA=idre.sales OUT=sales; *OUT= is optional;
BY Salary;
RUN;

Sorting can also be done using more then one variable.

PROC SORT DATA=idre.sales OUT=sales;
BY Salary Country;
RUN;

As you can see, the data is sorted in ascending order by “Salary ” first and then when there are tied salaries from different countries, AU comes before US alphabetically.  We can change this sorting behavior flipping the ordering of our variables and/or adding in the DESCENDING option, which reverses the sort order for the variable that immediately follows it.

PROC SORT DATA=idre.sales OUT=sales;
BY DESCENDING Salary DESCENDING Country;
RUN;

4.4.2 Merging

One data management task that requires proper sorting is merging. Merging involves matching one observation in a dataset to one observation (One-to-One) or multiple observations (One-to-Many) in a second dataset. In order for this to be done properly in SAS, the datasets to be merged must be sorted by the same variable(s).  In the example below, we will merge a dataset that has employee payroll information with a second dataset with employee addresses. Since an employee’s ID number (employee_id) is a unique identifier of each observation, we will use this variable to match observations.

First, we need sort each dataset by employee_id.

PROC SORT DATA=idre.employee_payroll OUT=payroll; 
BY Employee_ID;
RUN;

PROC SORT DATA=idre.employee_addresses OUT=addresses;
BY Employee_ID;
RUN;

A few things to take note of. First, You will notice that datasets “addresses” and “payroll” do not share any of the same variables except Employee_ID. In general, you do not want to merge datasets that include variables with the same names. SAS can only use one set of values and will arbitrarily choose the values from the last dataset read.  Thus, you should rename variables before attempting the merge.  Second, Employee_ID is unique in each dataset, so this will be a One-to-One merge.

Merging is done in a Data step similar to what we have been executing, except instead of the SET statement we now have a MERGE statement. Additionally, the BY is used to tell SAS which variable will be used to match records. The variable after the BY statement is the same unique identifier that we just used for sorting.

DATA payadd;
MERGE payroll addresses;
BY Employee_ID;
RUN;

Below is a subset of variables from the newly merged data. As you can see, Employee_Name is from the “addresses” data and Birth_date and Salary are from the “payroll” data.

Now let’s take a look at an example of a One-to-Many merge.

The first set of data provides information on order and delivery dates.  In the second set of data we have information on the product or products ordered. Because more then one item can be associated with a particular Order_ID, it is not unique in this dataset. Thus, we will need to conduct a one to many merge where each row in our “orders” data could be merged with multiple rows in the “order_item” data. Again, we will begin by sorting both sets of data by Order_ID.

PROC SORT DATA=idre.orders OUT= orders;
BY Order_id;
RUN;

PROC SORT DATA=idre.order_item OUT= order_item;
BY Order_id;
RUN;

Below is our syntax to merge the two data sets. Notice we also used a KEEP statement. This allows us to merge the data and control the number variables present in the final merged dataset.

DATA allorders;
MERGE orders order_item;
BY Order_ID;
KEEP Order_ID Order_Item_Num Order_Type Order_Date Quantity Total_Retail_Price;
RUN;

Above is a selected portion of the merged data. SAS executed the merge without an error but it appears that we have some missing data as a result. The two variables that have missing information were both from the “orders” data. This is an indication that we perhaps have some non-matches. If we go back and look at the “orders” data we would see that there is no information for the order identifier “1243854878” but there is information in “order_item”, thus when you merge the datasets together all the variables from “orders” will have missing values for this particular order. There are a couple ways you can deal with this issue. First, you can leave the data as is with missing information for non-matches. Alternatively, you can choose to control the observations output to the new merged data set by using the IN option on the MERGE statement. The IN option a creates variable indicating which dataset(s) contributed to forming the observation in the final merge dataset. It is a temporary variable used in the merging process that is given a 0 value if did not provide information or a 1 if it did. We could then use this variable to select observations in the newly merged data that come from one dataset or both. Let’s take a look at how we could apply this option in our previous merge.

DATA allorders2;
MERGE orders (in=a)             
      order_item (in=b);
BY Order_ID;
KEEP Order_ID Order_Item_Num Order_Type Order_Date Quantity Total_Retail_Price;
IF a;
RUN;

Using the IN option with an IF statement selects observations to be matched by order_ID that are present in “orders”. If you have a value for order_ID that is in “order_item” but not “orders” then it will not be used to construct observations for the “allorders2” data set. Note: Using IF=a is equivalent to saying IF a=1. Thus, you will not end up with any missing values.

Now, one thing you generally want to avoid is many-to-many merges. When neither dataset has a unique identifier that will allow for proper matching of records the result is a somewhat unpredictable and often undesirable assorting of observations.

4.4.3 Appending

Appending or concatenating observations is the process of adding rows or observations to a dataset as opposed to merging which adds variables. This can also be accomplished using a Data step. SAS will stack the columns to together by matching the names across datasets.

We will append three datasets that include information on orders from 3 consecutive months (July-September) in 2011.  Below is snapshot of the first two records from each of the datasets to be appended.

The syntax to conduct the append is quite simple.  All you have to do is list the datasets to be appended on the SET statement line. The ordering and the number of datasets does not matter.

DATA mnth7_8_9_2011 ;
SET idre.mnth7_2011 idre.mnth8_2011 idre.mnth9_2011;
RUN;

A portion of the newly appended dataset is below.

Now you can see that all 3 datasets has been appended or “stacked” together. This example worked perfectly because the three datasets shared the exact same variables. But what happens when you append the datasets that do not contain the same variables?

Take a look back at our “shoe” data. Below we have two sets of data, one for Eclipse shoes and one for Tracker Shoes. You will notice they share all the same variables except two, product_id and supplier_name.

What will happen when we attempt to append the data?

DATA shoes;
SET idre.shoes_eclipse idre.shoes_tracker;
RUN;

The append still executes without error. However, in the new “shoes” data we created, all the records from the Eclipse dataset will be missing on the variables that were only in the Tracker dataset.

5.0 Modifying SAS Output

5.1 Titles and Footnotes

As you have probably already noticed, SAS provides a lot of output from many of it’s procedures. As a researcher it is important to know how to manipulate and change your output to convey important information to your audience. One of the procedures we have been using to obtain output from our various Data steps is PROC PRINT.  We will begin by exploring some ways of enhancing the output from this procedure.

Whenever you are presenting tables of information, the first item most people look for is a title. This is easily implemented with a TITLE statement in SAS. Additionally, it is also possible to add multiple titles to output in SAS as well as footnotes by just adding a numeric suffix to the statement indicating the desired ordering. SAS allows for up to 10 different titles and/or footnotes.

TITLE1 'Orion Star Sales Staff';
TITLE2 'Salary Report';
FOOTNOTE1 'Confidential';

PROC PRINT DATA=idre.sales (OBS=5);
VAR Employee_ID 
Last_Name Salary;
RUN;

5.2 Label Options

Additionally, you may also find it useful to label your variables for added readability. You can do this with the LABEL statement. You will notice that some of the variable labels are longer than others. When you only have three variables it may not seem important, but if you have to label 10 variables, available space in a table may need to be a consideration. One way to deal with this is to use the SPLIT option on the PROC PRINT line. This allows the user to control the display of the title so that instead of the label being one line, you can split it into two lines.

PROC PRINT DATA=idre.sales (OBS=5) SPLIT='*';
VAR Employee_ID Last_Name Salary;
LABEL Employee_ID = 'Sales ID' 
      Last_Name = 'Last*Name' 
      Salary = 'Annual*Salary';
RUN;

The variable names have been replaced with labels.  Note that we did not have to resubmit the TITLE and FOOTNOTE statements. These are considered global statements and remain in effect until you cancel them or you end your SAS session. To cancel these you must issue a blank statement for each:

TITLE;
FOOTNOTE;

5.3 Formats

Beyond just labeling variables, you may also want to properly label the values of those variables. This is carried out in SAS using a FORMAT statement. Formatting values changes the appearance of those values in output but the underlying values does NOT change.

SAS has pre-defined formats for certain types of variables like dates and allows users to create their own formats for specific situations. Earlier we saw some examples of pre-defined formats when we covered date functions. Here, we will focus on how to create and apply user-defined formats.

In SAS, the PROC FORMAT procedure is used to define formats. For example, take a look at the syntax below:

PROC FORMAT;
VALUE $ctryfmt     'AU'='Australia'
                   'US'='United States' 
                    other ='Miscoded'; 

VALUE tiers         0-49999='Tier 1' 
                    50000-99999='Tier 2'
                    100000-250000='Tier 3';
RUN;

Each format is defined after a VALUE statement. The name you choose is up to you. Notice that character formats must be defined with a “$” in front of them. You then provide a value label for each level or range of values. The first format we are creating is to label the countries will their full names instead of abbreviations. VALUE statements can also use keywords. In this example other specifies that any values other then AU or US will be labeled as “Miscoded’.  For numeric formats, you can label a single value or a range of values. Once the formats are created we can apply them to the variables of interest.

In both Data steps and Proc steps, SAS distinguishes formats from variables by ending them in a period which then turns the text green.

PROC PRINT DATA=idre.sales (OBS=5);
VAR Employee_ID Salary Country Birth_Date Hire_Date;
FORMAT Salary tiers. Birth_Date Hire_Date monyy7. Country $ctryfmt.;
RUN;

Above you can see the appearance of the table with unformatted values to the one with formatted values. If you only want to use the formatted values for certain procedures in SAS, then you can just add a format statement as we did above. If you want these formats to be permanently applied to a variable, then you can use the same format statement in a Data Step.

5.4  Output Delivery System (ODS) Basics

Besides customizing the SAS default output, you may want to output results to different file types.  By default SAS 9.4 output results as HTML and this is what you see in the “Results Viewer” window. If you would like to change this behavior, you will need to use the Output Delivery System (ODS) statement.  This will allow for output in several different formats including listing/text, rtf, pdf and .xls.

ODS statements are also global statements are in effect until closed. The statement comes before running the procedure. Once the procedure(s) is executed, you will then close the ODS statement. The basic syntax is shown below:

ODS PDF FILE="&pathexample.pdf";
ODS RTF FILE="&pathexample.rtf";

PROC FREQ DATA=<data>;
TABLES <variable>;
RUN;

ODS PDF CLOSE;
ODS RTF CLOSE;

In this case, the output from the PROC FREQ will be saved to a pdf file and a rtf file. You must also specify the path or location where you want these documents saved. Once complete you should then close the output destination otherwise SAS will keep sending your results to these documents.

Before SAS 9.3 the default output destination was listing.  You can see the results from the PROC FREQ in the Output window and see a new icon in the Results tab.

ODS LISTING;
PROC FREQ DATA=idre.sales;
TABLES gender;
RUN;
ODS LISTING CLOSE;

As a user, you can also customize the style or look of the output when selecting either a html, pds or rtf destination. This ability is often useful when formatting results for presentations or publications.  Below are some examples of the different options available in SAS:

ODS HTML FILE="C:myreport.html" STYLE=sasweb;
PROC FREQ DATA=idre.sales;
TABLES gender;
RUN;
ODS HTML CLOSE;

ODS PDF FILE="C:myreport.pdf" STYLE=printer; /*Default*/
ODS PDF FILE="C:myreport1.pdf" STYLE=journal; 
PROC FREQ DATA=idre.sales;
TABLES gender;
RUN;
ODS PDF CLOSE;

Just to be safe we will go ahead and close all of the open destinations. However, be mindful that this will also close the html default, so you will need to reissue the global statement turning it back on. Otherwise the next time you issue a procedure that generates output, SAS will issue the warning “No output destination active”.

ODS _ALL_ CLOSE;
ODS HTML;

6.0 Special Issues

6.1 Dealing with Duplicates

An issue that comes up a lot in data management is how to handle duplicates. There are several ways in SAS to identify duplicate records.

One way is to use some of the options available to us with the PROC FREQ procedure. In the “nonsales” data file, we should have 235 unique employee identification numbers. We can use the ORDER=FREQ option to determine if this is true. This option displays the frequency of each unique identification number in descending order.

PROC FREQ DATA=idre.nonsales ORDER=FREQ;
TABLES Employee_ID;
RUN;

Above you can see that the employee ID #120108 has two records associated with it, indicating that we have a duplicate problem. Another useful option is NLEVELS, which displays the number of distinct values for each variable.

PROC FREQ DATA=idre.nonsales NLEVELS;
TABLES Employee_ID /NOPRINT;
RUN;

There are 235 unique employees in the “nonsales” data but only 234 unique levels, meaning that one employee ID# is duplicated.

Once the presence of duplicate ID numbers has been confirmed, you will most likely want to examine them to determine if they are indeed duplicate records or if the employee ID number is incorrect. In our dataset only one ID is duplicated making assessment fairly easy. However, what do you do when several ID’s or records are duplicated?  Let’s separate the records with unique ID’s from the duplicates using an IF statement.

PROC SORT DATA=idre.nonsales OUT=ids2;
BY employee_id;
RUN;

DATA dupes nodupes;
SET ids2;
BY employee_id;
IF NOT (FIRST.employee_id and LAST.employee_id) THEN OUTPUT dupes;
ELSE OUTPUT nodupes;
RUN;

Above we are using the keywords FIRST. and LAST. These keywords identify the first and last record in the grouping variable indicated after the BY statement. When an employee ID is unique, the first and last record will be the same row.  Thus our code outputs employee ID’s where the first and last records are not the same, to a dataset called “dupes”, and all the other unique records are put in dataset called “nodupes”.

“Dupes” or duplicated employee ID numbers:

“NoDupes” or unique employee ID numbers.

6.2 Identifying Outliers

Another issue that comes up a lot in dealing with data is outliers. The simplest way in SAS to identify outliers in to use the UNIVARIATE procedure.

By default, the UNIVARIATE procedure outputs the 5 highest and lowest extreme observations. Let’s examine outliers for product prices in the “price_new” dataset.

PROC UNIVARIATE DATA=idre.price_new;
VAR unit_cost_price;
RUN;

You can override this default by specifying the option NEXTROBS = on the procedure line and indicate the number of outliers to display. You can specify any number between 0 and half of the total observations. You will also notice that along with the extreme values, SAS also provides an observation or row number that corresponds to this value. Additionally, you can also use the ID statement to identify observations. This statement specifies one or more variables to be included in the outliers table. Let’s try adding the product identifier Product_ID to each of our extreme values.

PROC UNIVARIATE DATA=idre.price_new NEXTROBS=3;
VAR unit_cost_price;
ID Product_ID;
RUN;

Now each extreme value is associated with it’s ID number.

7.0 Wrapping things up

As we stated in the beginning, SAS is a very flexible programs with great features for data management.

This seminar only scratches the surface on describing all of the programming options available to users.

For more information on the topics discussed here please explore our website.

Additionally, SAS has a host of courses designed to improve your programming skills aimed at users of all levels.