Lesson 32: Introduction to SAS SQLLesson 32: Introduction to SAS SQL
PROC SQL is a powerful tool for data manipulation and querying. It can perform many functions such as the conventional DATA and PROC steps but with fewer statements and computer resources. In this lesson, we will investigate how to select, subset, sort, summarize and group data with SQL procedure.
- Know about the basic syntax of PROC SQL and its uniqueness
- Use SELECT and FROM clause to query a single table
- Display the result of a query in predefined ODS destination
- Generate and name the new table with the CREATE TABLE statement
- Use the shortcut (*) to select all rows in a table
- Create new columns with SELECT clause
- Use options in SELECT statement to change variables’ format, label, and length
- Use OUTOBS= option in PROC SQL statement to limit the number of rows in display
- Use CASE operator inside the SELECT clause to conditionally process data
- Use WHERE clause and its operators to subset a table
- Insert the keyword CALCULATED inside the WHERE clause to subset a table based on the calculated results
- Use ORDER BY clause to sort data in ascending order by default
- Use its location number in SELECT clause to reference a column
- Add the keyword DESC to sort data descendingly
- Summarize data with aggregation functions provided in PROC SQL
- Use multiple columns as arguments inside a summary function
- Select column(s) besides a summary function with no GROUP BY clause
- Use GROUP BY clause with summary functions to summarize groups of data
- Use COUNT() function to count non-missing values of rows of a table, a column, or non-duplicate values
- Use HAVING clause to subset data
- Understand the differences between HAVING and WHERE clause
- Query multiple tables by listing multiple tables in the FROM clause
- Understand a Cartesian product in SAS SQL
- Use the WHERE clause to complete the inner join
32.1 - Proc SQL Basics32.1 - Proc SQL Basics
PROC SQL is a procedure that SAS developed for the implementation of Structured Query Language. You can use this procedure to modify, retrieve and report data in tables and views (created on tables). Just as with other SAS procedures, PROC SQL also has basic syntax structures. It takes the following general form:
PROC SQL; SELECT column-1<,…column-n> FROM table-1|view-1<,…table-n|view-n> <WHERE expression> <GROUP BY column-1<,…column-n>> <HAVING expression> <ORDER BY column-1<,…column-n>>; QUIT;
First of all, you may see differences in terminology between SQL and other SAS steps. For example, the data file is called data set in other SAS steps, but table in SQL. Correspondingly, records are called observations in the previous lessons, but rows in SQL tables; and we call a field of data set as variable, but column in this lesson.
|Other SAS steps||SQL Procedure|
Another thing that needs your attention is that, unlike other SAS procedures, there could be one or a few SELECT statements inside PROC SQL. One SELECT statement is called a query, which is composed of many clauses, like SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY. The order of these clauses is important. They must appear in the order as shown above.
We will use the whole lesson to work our way through all these keywords in PROC SQL. Let’s start with the most basic one.
The following SAS SQL code is just query that retrieves data from a single table:
libname stat482 "X:\MG\stat480~483\proc sql"; PROC SQL; select ID, SATM, SATV from stat482.survey; QUIT;
To run the program above, you will need to save the SAS data file (survey.sas7bdat) to your computer first. Right-click the link and select the “Save link as…”. A save dialog box will appear and allow you to save the file to the location you choose on your computer. Edit LIBNAME statement to reflect the directory in which you saved the survey data set. Then run the program and check the output.
The SQL procedure in this code represents the most basic form of the procedure. Like other SAS procedures, you need to run PROC SQL at the beginning to invoke it. Inside the procedure, there is only one statement starting with SELECT, which chooses the columns you want. You can list as many columns as needed, separated by commas. Another clause is FROM, which is used to specify the table(s). PROC SQL follows the same protocol of SAS file names. Here we used a two-level name to reference the permanent file. Just as you read the code, this program is used to select three columns (student id, SAT Math score and SAS Verbal score) from the table.
If you use the SAS list output as default, the results of a query will be displayed in the SAS output window. For SAS9.2 and above, the default output will be html. You can also change the open ODS destinations to pdf, rtf or others for the output.
The following SAS program changes the ODS destination from listing to pdf. So the output will be saved under the predefined directory as pdf file.
ods listing close; ods pdf file="X:\MG\stat480~483\proc sql\sat_scores.pdf"; PROC SQL; select ID, SATM, SATV from stat482.survey; QUIT; ods pdf close; ods listing;
The PROC SQL part in the above program is the same as the previous example. The added code is to close the current listing output and open the pdf destination and to save the pdf output generated by SQL procedure that follows to the specified file name. The end part of ODS statements tell SAS to close the pdf destination and open listing again.
In some cases, you may want to create a new table from a query. To do so, you need the CREATE TABLE statement with keyword AS and along with other clauses used in PROC SQL, such as SELECT, or FROM. Below is the basic syntax of CREATE TABLE statement from a query result.
CREATE TABLE table-name AS SELECT column-1<,…column-n> FROM table-1|view-1<,…table-n|view-n> <optional query clauses>;
The following SAS program uses CREATE TABLE statement to create a new table named SAT_scores, which contains student id, SAT math scores and verbal scores.
PROC SQL; CREATE TABLE SAT_Scores as select ID, SATM, SATV from stat482.survey; QUIT;
Launch and run the SAS program. You may notice that there is no output displayed in the SAS output window or any open ODS destination. That’s because the CREATE TABLE statement suppresses the printed output of the query. However, if you check the SAS log window, it shows a message that indicates that the table has been created, and the number of rows and columns in the table.
In this example, table SAT_scores has 226 rows and 3 columns. And the new table’s columns have the same attributes (type, length, format, label) as those of the selected source columns.
From the previous three examples, you now have some idea about what PROC SQL is like to work with. Let’s summarize what makes it so unique from other SAS procedures.
- Unlike other SAS procedures which contain many statements, the SQL procedure may consist of one or more than one SELECT statement. Each SELECT statement contains several clauses, like SELECT, FROM, WHERE, ORDER BY. But the SELECT and FROM clause are essential and indispensable. Other clauses are optional. All clauses have to be written in the order as listed in the syntax. For each one SELECT statement, only one semicolon is needed at the end of the statement.
- No RUN statement is required for PROC SQL to execute. SQL procedure will continue to run after you submit the program. To end it, you have to submit another PROC step, a DATA step, or a QUIT statement.
32.2 - Using the SELECT Clause32.2 - Using the SELECT Clause
In the previous section, we learned the basics about PROC SQL. Next, we will investigate more details about the SELECT statement and how to use it to retrieve data, create new columns and what options are available for data manipulation.
The following SAS program creates a new temporary table with all columns retrieved from permanent file traffic.sas7bdat:
PROC SQL; CREATE TABLE traffic as select * from stat482.traffic; QUIT; PROC CONTENTS data=traffic VARNUM; RUN; PROC CONTENTS data=stat482.traffic VARNUM; RUN;
First, you need to download the permanent SAS data file traffic to your own computer. Revise the
libname statement as needed. Then run the program.
One thing you need to know about this program is the shortcut, noted with an asterisk (*) after SELECT. The asterisk refers to all columns in the original table. So, this code is to select all columns in the permanent file into the temporary file, traffic.
To check the data, you may use the other procedures we learned in previous lessons, such as the PRINT procedure. In the above program, PROC CONTENTS has been used to check the variable attributes in the original and the new table. As we mentioned in the previous section, the variables chosen from other table(s) keep the same attributes.
Besides selecting original columns, the SELECT clause can also be used to create new columns, just as we used assignment statements in DATA step to create new variables.
The following program is to create new columns with the SELECT statement:
PROC SQL; select id, count_location, scan(count_location,-1,' ') as orientation, street, passing_vehicle_volume * 0.5 as weekends_traffic_volume from traffic; QUIT;
As you can see, this code uses the traffic table we created previously. Using the SELECT statement you can create new columns that contain either characters or numbers. With valid statements within the SELECT clause, you can use any expression for new columns. And, the new columns can be named by using the keyword AS followed by the names you would like to use. (Column names also follow the rules for SAS names.) In the above code, the first new column is created by a character function
scan(), which substring is the orientation information from the existing column, count_location. The name for this new column is
orientation after AS. (It may make no sense, just for the use of example.) The second new column is a math expression that estimates the traffic volume during weekends by multiplying daily vehicle volume by 0.5. Its alias is
Launch and run the SAS program, and review the output to convince yourself that SAS does indeed create two new columns as you expect. But you should note that new columns only exist during the query, unless you created a table out of it.
While observing the data in traffic, you may notice that some data are not formatted as you want. Fortunately, SAS provides many options in SELECT statement so you can enhance the appearance of the query output.
The following program adds the format to dates, labels columns and add titles to the output:
PROC SQL; TITLE "Traffic volume in Area SS"; TITLE2 "During weekdays and weekends"; select id, Date_of_count label='Date of Count' format=mmddyy10., count_location label='Location', street, passing_vehicle_volume label='Daily Volume' format=comma6., passing_vehicle_volume * 0.5 as weekends_traffic_volume label='Weekends Volume' format=comma6. from traffic; QUIT;
Launch and run the SAS program and then review the resulting output to convince yourself that the data has been formatted and labeled as you expect. Except for titles, you can also add a footnote to the output using footnote statement. But unlike using title and footnote statements with other SAS steps, both statements have to be placed either before the PROC SQL statement, or between the PROC SQL statement and SELECT statement.
One more thing we will talk about in this section is the CASE operator, which just follows the SELECT clause to create new columns conditionally. You must remember that this applies only to IF-THEN-ELSE statements that are available in DATA step. In PROC SQL, the CASE operator can perform the equivalent functions. First, let’s look at the syntax for the CASE construct.
CASE WHEN when-condition THEN result-expression <… WHEN when-condition THEN result-expression> <ELSE result-expression> END AS < column name>
As in IF-THEN statements, you can add as many WHEN conditions as you want. The conditions can be any valid SAS expression, including calculations, functions, and logical operators. It works as IF-THEN statements, too. If the conditions have been met, SAS will carry out the corresponding actions following the keyword THEN. If the WHEN condition is false, then PROC SQL executes the ELSE expression. You can create a new column and name it with AS keywords after END. The ELSE and AS keywords are optional. But it’s good practice to keep original columns while creating new ones.
The following SAS program uses CASE operator to assign different salary raise plans for each salary range:
PROC SQL ; select Name, Department, employee_annual_salary label='salary' format=dollar12.2, 'next year raise:', case when employee_annual_salary=. then . when employee_annual_salary < 85000 then 0.05 when 85000 <= employee_annual_salary < 125000 then 0.03 when employee_annual_salary >=125000 then 0.01 else 0 end as raise format=percent8. from stat482.salary; QUIT;
You already know format and label options from the previous explanations. There are a couple of new things in this example, however. First, you can insert a character(or numeric) constant as a new column in the table. Here a character string “next year raise” has been added between salary and raise. Raise is also a new column which has been created by the CASE operator based on the current annual salary of each person.
Download the SAS data set salary.sas7bdat on your computer and revise the
libname statement to reflect the directory where you save the file. Then launch and run the program. Review the query result to convince yourself that the raise values have been assigned correctly.
The CASE operator has two forms of syntax. In fact, if you use only one column for WHEN condition(s), this column’s name can be put after CASE and before WHEN. So you don’t have to repeat the column’s name in each WHEN condition. Below is the syntax for this form:
CASE <column-name> when-condition THEN result-expression <… WHEN when-condition THEN result-expression> <ELSE result-expression> END AS < column name>
The following program uses the simpler form of CASE construct to decide compensation (Yes or N/A) based on departments:
PROC SQL outobs=20; select Name, Department, employee_annual_salary label='salary' format=dollar12.2, case department when 'POLICE' then 'Yes' when 'FIRE' then 'Yes' else 'N/A' end as Compensation from stat482.salary; QUIT;
The above code uses the same data set as the previous example, salary. It assigns the different compensation plans based on which department people work for and creates a new column, Compensation, for the result. This time, the column name Department has been put outside the WHEN conditions and into CASE operator. So we don’t need coding like “WHEN department=’POLICE’” any more.
Another feature is the option you can use in the PROC SQL statement, OUTOBS=n. It can be used to limit the number of rows displayed in the output. So in this case, we would expect the table in the output window shows the first 20 rows of the data. And such a warning message will be delivered in the log file.
WARNING: Statement terminated early due to OUTBOS=20 option.
Note that OUTOBS= will also affect tables that are created by the CREATE TABLE statement.
Launch and run the program. Then check the query result to make sure the records have been processed as expected. Note that you have to be cautious with this simpler form. For instance, if you move Employee_annual_salary out of the WHEN conditions in the program of Example7, SAS will report an error and not execute!
32.3 - Using the WHERE Clause32.3 - Using the WHERE Clause
As you know, the WHERE statement or option in DATA step or other procedures is very useful in selecting observations from a data set based on some criteria. In PROC SQL, the WHERE clause in the SELECT statement can also be used to subset data based on specified conditions. Any valid SAS expression can be put inside WHERE clause, including functions, comparison or logical operators and even some special operators. Making the good use of it can increase programming efficiency and save computing resources greatly. As always, we will work through this subject with examples.
The following example uses the WHERE clause to select employees who work at a police department and have the job title as sergeant:
PROC SQL; select Name, Department, Employee_annual_salary from stat482.salary where Department='POLICE' AND Position_title='SERGEANT'; QUIT;
To run this program, please download the SAS data set salary.sas7bdat and save it to your computer.
Reading through the program, you must have known that it selects the name, department and annual salary information from salary data for police sergeants. Note that the columns in the WHERE clause do not have to be specified in the SELECT clause, (such as Position title), which is used in the WHERE clause but not in the SELECT clause. However, for the sake of the results checking, I would suggest to keep these columns in the query until verified.
Launch and run the SAS program, and review the output to convince yourself that the records have been selected as described.
We saw two types of operators used in the above program, the comparison (=) and the logical (and). Besides these common ones, another type that could be very useful in your programming is called a conditional operator. You may know some of them already, like IN, CONTAINS and MISSING. You can find the complete list of operators in the SAS documentation. Next, let’s look at a couple of examples on this using BETWEEN AND and LIKE.
BETWEEN value-1 AND value-2
Both value-1 and value-2 are end values. So you can use the BETWEEN AND operator to specify a range of values, such as from one date to another, or from lower limit to upper limit. The smaller value does not have to be the first.
The following program uses the operator, BETWEEN AND, to select observations from salary data whose annual salary is between \65,000 and \70,000, and also works in Fire department:
PROC SQL; select Name, Department, Employee_annual_salary label='Salary' format=DOLLAR12.2 from stat482.salary where Employee_annual_salary between 65000 and 70000 and Department='FIRE'; QUIT;
Launch and run the SAS program, and review the query output to convince yourself that the SAS yield the result as expected.
Column LIKE ‘pattern’
With the LIKE operator, you have to specify a column name and the pattern to be matched. Regarding the pattern, first it is case-sensitive and has to be enclosed in quotation marks; secondly, it may contain a special character, either an underscore(_) and/or percent sign(%). The underscore character stands for any single character and the percent sign for any sequence of zero or more characters. For example, assume that you are working with a table containing these values for a column.
Now using different patterns, the selection results are different:
|Kath%||Kathy, Kathie, Katherine|
|_ath%||All of the names above|
The following program shows the use of the LIKE operator in a WHERE clause to select name, department, position title and annual salary information for people whose name starts with R and the third letter is B:
PROC SQL; select Name, Department, Position_title, Employee_annual_salary label='Salary' format=DOLLAR12.2 from stat482.salary where Name like 'R_B%'; QUIT;
Launch and run the SAS program, and review the query output to convince yourself that the SAS behaves as described.
Another point worthy of being made here is the CALCULATED keywords. In the last section you learned that we can perform calculations in SELECT statement and assign an alias to that new column. However, because SAS processes the WHERE clause prior to the SELECT clause, you will run into a problem if the calculated column is used in a WHERE clause as condition. Therefore, the keyword CALCULATED has to be inserted into the WHERE clause along with the alias to inform SAS that the value is calculated within the query. This point will be illustrated by the following programs.
The following program attempts to calculate the bonus for every employee, then select ones who has more than $2,000 as bonus:
PROC SQL; select Name, Department, Employee_annual_salary label='Salary' format=DOLLAR12.2, Employee_annual_salary * 0.02 as Bonus from stat482.salary where Bonus >2000 ; QUIT;
Launch and run the SAS program. You may want to see what’s going wrong yourself. In the log window, SAS delivered an error message that the column Bonus cannot be found.
ERROR: The Following columns were not found in the contributing tables: Bonus.
That’s because SAS processes the WHERE clause before the SELECT clause. To make it right, add CALCULATED in the WHERE clause as shown below.
PROC SQL; select Name, Department, Employee_annual_salary label='Salary' format=DOLLAR12.2, Employee_annual_salary * 0.02 as Bonus format=DOLLAR10.2 from stat482.salary where CALCULATED Bonus >2000 ; QUIT;
Now it’s working! Make the same change to your program. Check the output to make sure that SAS processes the data properly.
An alternative to using the keyword CALCULATED is to repeat the calculation expression in the WHERE clause. In the preceding program, the WHERE clause can be rewritten as:
where Employee_annual_salary *0.02 >2000;
But note that this is not an efficient way to do this because SAS has to do the calculation twice.
32.4 - Sorting Data32.4 - Sorting Data
The SELECT and FROM clauses are indispensable in the SELECT statement. Other clauses may be optional but very useful when querying a table. Last section introduced the WHERE clause and how to use it to select rows conditionally. From previous examples, you may have noticed that the order of observations might remain the same as they were in original data set. If, however, you want to specify the order of data, you will need the ORDER BY clause to sort the data as you want.
The following SAS program uses the ORDER BY in SELECT statement inside PROC SQL to sort the data in the file survey.sas7bdat by the values of gender and GPA:
PROC SQL; select ID, Gender, GPA, SATM, SATV from stat482.survey where SATV is not null and GPA>3 order by Gender, GPA ; QUIT;
Download survey data to your own computer and change the directory if needed to reflect the local directory where you saved the file. Then launch and run the SAS program, and review the output to convince yourself that the query result is in order first by gender and then by GPA.
Several things need to be pointed out regarding the above program:
- You can use one or more column in ORDER BY to sort the data. Comma is used to separate multiple column names. In this example, two columns have been used, Gender and GPA. So the data will be sorted by Gender first, then by GPA in order.
- By default, the values of column(s) will be sorted ascendingly. For example, there are two values in Gender, Female and Male. In the query result, Female records are listed first, then male ones because SAS sorted them by the first letter in alphabetical order. As to GPA order, since it’s numeric, SAS sorted observations by number values of GPA inside each gender group.
- TheWHERE clause is used to select observations that her/his SAT verbal score is not missing and GPA greater than 3. “is not null” and “is not missing” are interchangeable to indicate no missing values included.
As in PROC SORT, if you want to change the default ascending order into descending order, you just need to specify DESC following the column name.
The following SAS program sorts the data survey.sas7bdat by the values of gender in descending order then by GPA ascendingly:
PROC SQL; select ID, Gender, GPA, SATM, SATV from stat482.survey where SATV is not null and GPA>3 order by Gender desc, 3 ; QUIT;
There are only two places that are different from the program in the previous example. DESC has been added after Gender to tell SAS to sort the data descending. Another way to refer to the column rather than its name is its location in the SELECT clause. GPA is listed as the third one so that we can use 3 to specify GPA.
Launch and run the SAS program, and then review the output to convince yourself that the output from this query is in descending order of Gender and in ascending order of GPA.
Up until now, you might think that ORDER BY can perform the same as PROC SORT. Actually, it can do more than that. Let’s find out with the next example.
The following program sorts the survey data first by gender in descending order as before, then by mean values of SAT math and verbal scores in ascending order:
PROC SQL; select * from stat482.survey where SATV is not null and GPA>3 order by Gender desc, MEAN(SATM,SATV) ; QUIT;
Since all columns will be used in the query, * is used to specify all columns after SELECT. The WHERE clause remains the same. In the ORDER BY clause, besides Gender, one function is used to calculate the average scores of SATM and SATV, then uses the calculation results to sort the data inside each gender group. To get the same result, could you try other SAS steps and count how many of them will be needed?
Launch and run the SAS program, and review the output to convince yourself that the data has been sorted in desired order.
One more thing, you may notice a note in log window when running this program.
NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.
That’s because MEAN(SATM,SATV) is not listed in the SELECT clause, only in the ORDER BY clause.
32.5 - Summarizing and Grouping Data32.5 - Summarizing and Grouping Data
In previous sections, we use the SQL procedure to generate detailed reports. Sometimes, the summarized report is also necessary for us to explore data. To do that, we are going to need summary functions and/or the GROUP BY clause in PROC SQL.
Many summary functions that are used in other SAS steps can also work well in PROC SQL. Below is the table of summary functions you can request:
|AVG, MEAN||mean or average of values|
|COUNT, FREQ, N||number of non-missing values|
|CSS||corrected sum of squares|
|CV||coefficient of variation(percent)|
|NMISS||number of missing values|
|PRT||probability of a greater absolute value of student's t|
|RANGE||range of values|
|STDERR||standard error of the mean|
|SUM||sum of values|
|T||student's t value for testing hypothesis|
|USS||uncorrected sum of squares|
Note: some functions have multiple names. The first listed is the SQL name.
Next we will work through examples to see how these functions perform calculations in PROC SQL. Along the way, the GROUP BY clause will be introduced and work with the functions.
In this section, the data from survey.sas7bdat will be used for all examples. First, make sure that the file is downloaded to your own computer and revise the libname as needed.
The following program uses the AVG() function to calculate the mean scores of SAT math and verbal test:
PROC SQL; select avg(SATM) as average_Math, avg(SATV) as average_Verbal from stat482.survey; QUIT
First launch and run the SAS program. When checking the output you will see two overall average scores have been calculated for SATM and SATV separately. There is only one observation in the output window.
Let’s review the function in the code. To calculate average, either MEAN() or AVG() can be used in this case. Note that there is only one argument (column) inside the function AVG(). So the statistic is calculated across all rows for one column.
AVG(STAM)—the overall average score of SATM
AVG(STAV)—the overall average score of SATV
Quite simple, right? Let’s add one more argument into the function. Can you guess how many observations will be in the output?
In the following program, two columns are the arguments of the function MEAN():
PROC SQL; select mean(SATM, SATV) as average from stat482.survey; QUIT;
We changed the program a little bit. Both SATM and SATV are put inside the function as arguments. Launch and run the SAS program. You will see there are 226 observations, which is the same as in the original survey data.
If you add more than one column as arguments of summary functions, SAS will perform the calculation across the columns for each row to generate the above output.
In this case, the summary function is not performing aggregation anymore. SAS then looks for a like-named function in BASE SAS. If yes, the calculation will be performed for each row; if not, an error message will be output in the log window. You can try to change MEAN() to AVG() to see what will happen.
ERROR: Function AVG could not be located.
The following program uses only one argument for MEAN(), but add one more column in the SELECT clause:
PROC SQL; select Gender, mean(SATM) as average_Math from stat482.survey; QUIT;
In the above program, the the SELECT statement changed again. This time, only one argument is for the MEAN() function to calculate the overall average score of SAT math grades. Outside the function, another column has been selected as well. What output will it produce?
Launch and run the SAS program. You may be surprised that the output contains 226 rows. Review the output you will see two things that have been done by the above code:
- It calculated the overall average math score for all students.
- It displayed all rows in the output because Gender is not an argument of MEAN() function.
Note that the overall average math score is just repeated for each row. You can find a message like the one below in the log window. When you submit such a program, SAS calculate the statistic first. Then merge it back with other columns. That’s how “remerging” happens.
NOTE: The query requires remerging summary statistics back with the original data
The above result is not what we wanted. Now, let’s see how to use the GROUP BY clause to make it reasonable.
The following example calculates the average SAT math score for each gender group:
PROC SQL; select Gender, mean(SATM) as average_Math from stat482.survey group by Gender; QUIT;
The above program seems identical to the program in the previous example except for one more clause: GROUP BY. Finally, we get it right and obtain the desired result: the average SAT math scores for female and male students. Of course, you can make further use of GROUP BY by adding multiple columns. Let’s find out with the next example.
The following program uses both Gender and SmokeCigarettes in the GROUP BY clause to calculate the average SAT math scores:
PROC SQL; select Gender, SmokeCigarettes, mean(SATM) as average_Math from stat482.survey group by 1, 2; QUIT;
Launch and run the SAS program, then review the output. As you can see, the average math scores are calculated for each smoking group (Yes or No) inside each gender group (Female or Male).
Just one more thing about this program, the columns can also be referred to by their locations in the SELECT clause as in the WHERE clause. Here, 1 and 2 are used to refer to Gender and SmokeCigarettes.
Next, we will pay attention to one special summary function in SQL, which is COUNT(). You can use the COUN() function to count the non-missing values.
The following example count the number of rows in survey data, the number of non-missing records for math and verbal test scores, and the distinct values of gender:
PROC SQL; select count(*) as No_obs, count(SATM) as No_Math_records, count(SATV) as No_Verbal_records, count(distinct Gender) as Gender_group from stat482.survey; QUIT;
The above code reveals three different common ways of using the COUNT() function.
- Count(*) is to count total number of rows in a table. COUNT() is the only function that allows you to use * as an argument.
- Count(column) is to count the number of non-missing values in a column. In the program, we count the number of non-missing values for math and verbal scores.
- Count(distinct column) is to count the total number of unique values in a column. In the above example, we count the number of gender categories.
Launch and run the SAS program, then review the output. With knowledge of some of the missing values inside the table, we are not surprised to see the first three numbers unmatched. The total number of rows in survey data is 226. The total numbers of non-missing values of math and verbal scores are 216 and 215, separately. Both numbers are less than 226, which means there are missing values in each column, and SATV has one more value missing. There are only two categories in Gender, Male and Female. So the last count is 2.
32.6 - Using the HAVING Clause32.6 - Using the HAVING Clause
Previously we learned how to use the GROUP BY clause to group and summarize data. Sometimes, we want to select certain groups from the result. That’s when the HAVING clause comes into play.
This section we are going to use the salary.sas7bdat data for all examples. Download this SAS data file onto your own computer and change the
libname to be the directory where you save the data.
The following program calculates the average salary for each department, then select three departments as needed in the query output:
PROC SQL; select Department, avg(Employee_annual_salary) as Avg_salary format=DOLLAR12.2 from stat482.salary group by Department having Department in ('LAW','FINANCE','FIRE') order by Avg_salary; QUIT;
Let’s review the program first. The code selects the column Department and uses the summary function AVG() to compute the average salaries. Since the GROUP BY clause also is also present in the SELECT statement, the averages are for each department. The user is only interested in three departments, Law, Finance and Fire. So we use the HAVING clause to select only these three to be output. Finally, we ask SAS to sort the data by average salaries. This program contains every clause we have learned so far except the WHERE clause, which we will address later.
Launch and run the SAS program and review the output to make sure you understand the output.
You may wonder if WHERE can do the same thing as HAVING does in the above program. You can try replacing Having with WHERE clause as following. You will get identical output as before.
PROC SQL; select Department, avg(Employee_annual_salary) as Avg_salary format=DOLLAR12.2 from stat482.salary where Department in ('LAW','FINANCE','FIRE') group by Department order by Avg_salary; QUIT;
However, let’s not assume that WHERE and HAVING are the same based on this. There are some big differences between them. Generally speaking, HAVING has control on grouped data during output; WHERE controls input data row by row. Let’s see more examples about these two commands.
The following program calculates the average salary for each department and choose ones having more than $70,000:
PROC SQL; select Department, avg(Employee_annual_salary) as Avg_salary format=DOLLAR12.2 from stat482.salary group by Department having Avg_salary > 70000 order by Avg_salary; QUIT;
Only a small change has been made to this program. The condition in the HAVING clause changed the department average salary more than $70,000. So, the expression used in the HAVING statement is a summary function. And, the data is sorted by average values.
Launch and run the SAS program and review the output. As we expect, all departments having more than $70,000 average salary are listed as the query result.
Next, let’s try using WHERE to perform the same task.
PROC SQL; select Department, avg(Employee_annual_salary) as Avg_salary format=DOLLAR12.2 from stat482.salary where calculated Avg_salary > 70000 group by Department order by Avg_salary; QUIT;
You must remember that to use the computed result in the WHERE clause, the keyword “CALCULATED” should be inserted. Oops! SAS gives us an error message like this:
ERROR: Summary functions are restricted to the SELECT and HAVING clauses only.
This example illustrates a big difference between HAVING and WHERE. The summary functions can be used in a HAVING clause but not in a WHERE clause, because HAVING works on grouped data, but WHERE evaluates existing or calculated data row by row.
Based on our current experiences with these two clauses, you might prefer to use HAVING since it can be used for both situations. However, don’t rush to this conclusion either. You will find out more in the next example.
The following two SAS program are similar. The only difference is that the first program uses a WHERE clause and the second program uses a HAVING clause. They try to accomplish the same task: count how many employees at each position inside Police Department:
PROC SQL; select Position_Title, count(*) as Employees from stat482.salary where Department='POLICE' group by Position_Title; QUIT; PROC SQL; select Position_Title, count(*) as Employees from stat482.salary group by Position_Title having Department='POLICE'; QUIT;
Now, Launch and run both programs. The output on the top is from the program using WHERE clause; the output on the bottom is the partial output from the program using HAVING clause.
You might be surprised to see how different these two results are. One would expect a result like the output on the top. But the output on the bottom has so many more rows, and even some numbers do not match! Let’s review the code to understand what happened. There are two columns in the SELECT clause, Position_Title and a summary function, count(*), which counts total number of rows for each position group since we specify Position_Title in the GROUP BY clause. Unlike the programs in the previous example, the expression used inside WHERE and HAVING references another column, Department, which is not in the SELECT clause. Therefore, SAS handles them differently in the two programs.
The first program uses the WHERE clause. Since SAS processes the WHERE clause before SELECT and on a row-by-row basis, the records from Police department are selected from the data first. Then SAS counts the number of employees under each position title inside the department. For example, there is only one person who is a “CLINICAL THERAPIST III” in the Police Department. So the count is 1. We obtained the desired output.
On the other hand, the second program uses the HAVING clause. It is equivalent to the following program but without Department column in the output:
PROC SQL; select Position Title, Department, count(*) as employees from stat482.salary group by Position_Title having Department='POLICE'; QUIT;
In this program, SAS counts employee numbers on each position across all departments because of GROUP BY clause. For example, there is each one person titled “CLINICAL THERAPIST III” in POLICE department and HEALTH department. So the total count on this position is 2. Since there is an extra column in SELECT clause besides the summary function and a GROUP BY column, all rows are in the output with counts on each job position. For instance, under position title “CLINICAL THERAPIST III”, both records have 2 as value of “Employees”. At last, SAS evaluates the condition (Department=POLICE) in HAVING clause to select rows for the output. That’s why you see Employees=2 for position title “CLINICAL THERAPIST III” in the output from the second query.
We have seen two examples that show the differences between HAVING and WHERE so far. Since SAS handles them so differently, when it comes to WHERE or HAVING, pick one that fits your needs the best.
Last but not the least, let’s check out one more cool feature of HAVING clause.
The following program selects the departments whose average salary is lower than the overall salary level:
PROC SQL; select Department, avg(Employee_annual_salary) as Avg_salary format=DOLLAR12.2 from stat482.salary group by Department having Avg_salary < (select avg(Employee_annual_salary) from stat482.salary) order by Avg_salary; QUIT;
Going through this program, you may not find anything unusual until HAVING clause. Inside the clause it’s not a standard expression as before, but a query:
(select avg(Employee_annual_salary) from stat482.salary)
Such kind of query is called subquery, inner query or nested query. You can use this query-expression in a HAVING or WHERE clause. The subquery used in this example is to calculate the overall average salary. The result is compared with average salaries of each department. Then SAS evaluates the condition “Less than” in HAVING clause to select departments who have less average salaries to output.
Launch and run the SAS program, and review the query result. Convince yourself that the departments’ information has been selected as described.
32.7 - Querying Multiple Tables32.7 - Querying Multiple Tables
So far, all the examples in this lesson are querying a single table. However, as matter of fact, you can specify multiple tables in the FROM clause. Querying more than more table at a time makes PROC SQL even more powerful in data manipulation.
The following examples use two tables:
Survey Data (survey.sas7bdat) contains:
ID, Gender, GPA, SmokeCigarrets, SATM, SATV
Survey2 Data (survey2.sas7bdat) contains:
ID, Seating, DiveInfluence, Height, Weight
Download these two tables if you have not done so. Revise the
libnameto reflect the directory that you save the files.
The following program attempts to get demographic information about students from two separate tables, survey and survey2:
PROC SQL; create table demo_info as select ID, Gender, Height, Weight from stat482.survey, stat482.survey2; QUIT;
Let’s review the code. In this SQL procedure, we used the CREATE TABLE clause to save and name the new table as
demo_info. The subsequent SELECT clause chooses ID, gender, height and weight columns from two tables. In FROM clause, two tables’ names are listed.
Launch and run the SAS program. You should expect no result in the output window because the CREATE TABLE clause suppresses output. On the other hand, check the log window and you will find the error message: “Ambiguous reference, column ID is in more than one table”.
ERROR: Ambiguous reference, column ID is in more than one table.
As you observed two tables, ID is in both tables and contains the same information. If a column in the SELECT statement appears in multiple tables, the table it is chosen from has to be specified by adding the table’s name in front as this:
So to make it right, we revise the previous program a little bit: change ID to survey.ID, which means that we use ID from survey data. The other change is the tables’ names. You can give a table an alias with or without the keyword AS after its original name. In the following program, we use S1 for survey data and S2 to survey2 data. And as you can see, it’s okay to use one level alias even for a permanent file. This makes life easier! In this way, ID can be specified as S1.ID.
PROC SQL; create table demo_info as select s1.ID, Gender, Height, Weight from stat482.survey as s1, stat482.survey2 as s2; QUIT;
Everything seems good. Now launch and run the SAS program. As before, there is no output because of the CREATE TABLE statement. Check the log file in which there are two notes that need your attention.
NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized.
NOTE: Table WORK.DEMO_INFO created, with 51076 rows and 4 columns.
The first is “ The execution of this query involves performing one or more Cartesian product joins that can not be optimized”. What is a Cartesian product? It refers to a query result in which each row in the first table is combined with every row in the second table. If you specify multiple tables in FROM clause but do not use a WHERE clause to choose needed rows, a Cartesian product is generated. For example,
If we submit the following program:
from table1, table2;
Table1 has 3 rows; Table2 has 3 rows as well. Their Cartesian product contains (3*3)9 rows.
In the program for this example, there is no WHERE clause. So SAS generated a Cartesian product and gave you the note. Both Survey and Survey2 have 226 rows in the table. The query should have (226*226) = 51076 rows as the result. That’s why you got the other note, “Table Work.demo_info created, with 51076 rows and 4 columns.” Clearly, this can’t be correct. How do we get the desired result? Let’s make a final push.
The following program selects the demographic information of students (ID, gender, height and weight) from two tables, survey and survey2:
PROC SQL; create table demo_info as select s1.ID, Gender, Height, Weight from stat482.survey as s1, stat482.survey2 as s2 where s1.ID = s2.ID; select * from demo_info; QUIT;
Let’s check through the code. Only one more clause has been added to the query, WHERE. We use the WHERE clause to subset the whole Cartesian product by only selecting the rows with matched ID numbers. Note that the column names in the WHERE clause do not have to be the same. At last, to be able to check the table in person, another query is added to display the data in the output window.
Launch and run the SAS program, and review the log file and the output.
NOTE: Table WORK.DEMO_INFO creates, with 226 rows and 4 columns.
Finally, we got what we want. As you can see from the query result, it’s like combining two columns from each table horizontally. SAS also call it join. In this particular case, since we only chose the matched rows, it’s also called the inner join. Such type of join is very similar to Merge By in the DATA step but requiring less computing resources and less coding. There are other types of join and data union (a vertical combination of rows) in PROC SQL which are beyond this lesson’s scope. If you are interested, you can explore them yourself with the foundation of this lesson!
32.8 - Summary32.8 - Summary
In this lesson, we’ve learned the basics of PROC SQL and how to use each clause of the SELECT statement to generate a query.
The homework for this lesson will give you more practice with the SQL procedure so that you become even more familiar with how it works and can use it in your own SAS programming.