What is SQL?¶
- Structured Query Language
- SQL is a widely used language to retrieve and update relational tables and databases.
- Q: How are relational databases different from data sets we have used in this class?
- Q: What is the different between a database and a table?
What can SQL do?¶
- Execute queries from against a database
- Retrieve data from a database
- Insert/update records in a database
- Create new databases/tables
- Compute summary statistics
- In SAS, the PROC SQL command can be applied to SAS data sets as an alternative to using DATA steps.
Database Lingo¶
- RDBMS (Relational Database Management System) is the basis for SQL and is used in all modern database systems.
- The data in a relational database is stored in object called tables.
- Databases typically contain several tables.
- Tables are analogous to datasets we have been using in class.
- For efficient computing only relevant data are loaded into memory, rather than an entire database.
- Tables contain columns called fields and rows called records.
SQL Syntax¶
A basic SQL cheatsheet is available at http://files.zeroturnaround.com/pdf/zt_sql_cheat_sheet.pdf.
There are many commands in SQL some of the most important are illustrated below:
SELECT col_name FROM table_name WHERE conditionUPDATE table_name SET col_name = 5 WHERE conditionINSERT INTO table_name (col_name1, col_name2,...) VALUES (value1, value2, ...)DELETE FROM table_name WHERE condition;SELECT COUNT(col_name) FROM table_name WHERE conditionSELECT table1.col1, table1.col2, table2.col1 FROM table1 INNER JOIN table2 on table1.key= table2.key
PROC SQL Syntax¶
Using SAS the PROC SQL procedure can be used to query datastate systems or SAS data sets. The syntax follows as:
PROC SQL;
SQL statements;
RUN;We will focus on SAS syntax, but there are minimal (or no) differences in other applications.
For this lecture and the lab, we will use a subset of baseball tables from https://www.kaggle.com/seanlahman/the-history-of-baseball.
- all_star
- batting
- college
- park
- pitching
- player
- team
libname STAT408 '/folders/myfolders/';
/* Note when using SAS OnDemand set this libname to
LIBNAME STAT408 "/courses/d716b355ba27fe300";
*/
SELECT¶
Select is the workhorse command for SQL queries. Select can be used to query data from tables.
A select query must have a SELECT statement specifying which columns to query and a FROM statement to identify the table. There are additional options below, which must follow this order.
- SELECT (variables to include in query, * is a wildcard for all columns)
- FROM (name of table)
- WHERE (condition for variables, applies to each record)
- GROUP BY (can aggregate by a variable, when specify a function of selected variables)
- HAVING (condition for aggregated variables specified in group by statement)
- ORDER BY (allows sorting of output)
Q1. Create a query from STAT408.player to obtain all the player first and last names for all players born in 1993.
Q2. Select all rows from STAT408.park where the park is in California.
/* Create a query from STAT408.player to obtain all the player
first and last names for all players born in 1993. */
PROC SQL;
SELECT name_first, name_last
FROM STAT408.player
WHERE birth_year = 1993;
RUN;
Out[7]:
*Select all rows from STAT408.park where the park is in California.;
PROC SQL;
SELECT *
FROM STAT408.park
WHERE state = 'CA';
RUN;
Using Group by and SQL aggregate functions¶
There are four main sets of aggregate type functions in SQL
- COUNT (returns the number of rows)
- SUM
- AVG
- MIN / MAX
Q: How do we write a query to calculate the total number of homeruns in each year since 1980?
*write a query to calculate the total number of homeruns in each year since 1980;
PROC SQL;
SELECT year, SUM(hr)
FROM STAT408.batting
GROUP BY year
HAVING year GT 1979
ORDER BY year;
RUN;
Creating and Modify Tables¶
Thus far we have only been querying tables and producing output. Next we will see how to create and modify tables.
Create a new table¶
PROC SQL;
CREATE TABLE table_name as
SELECT col1, col2, col3
FROM old_table;
RUN;Q1. Create a table called born93 with info from all players born in 1993.
*\ Create a new table with info from players born in 1993 \*
PROC SQL;
CREATE TABLE born93 as
SELECT player_id, birth_country, birth_state, name_first, name_last FROM STAT408.player
WHERE birth_year = 1993;
RUN;
Modifying a table¶
There are several ways to modify an existing table:
- INSERT INTO
- UPDATE
- DELETE
INSERT INTO¶
You can manually insert values into a table as follows:
INSERT INTO table_name (col1, col2, col3, ...)
VALUES (val1, val2, val3);Note the values can be computed from a SELECT statement as well
INSERT INTO table_name (col1, col2, col3, ...)
SELECT val1, val2, val3 FROM table_name2;Q: Write a PROC SQL command to add your name to the born93 table.
PROC SQL;
INSERT INTO born93 (player_id, birth_country, birth_state, name_first, name_last)
VALUES ('hoeghan01', 'USA','IA','Andrew','Hoegh');
RUN;
PROC SQL;
SELECT *
FROM born93
WHERE birth_state = 'IA';
RUN;
UPDATE¶
The update value allows you to update multiple records using a WHERE clause.
UPDATE table_name
SET col1 = value
WHERE col2= value;PROC SQL;
UPDATE born93
SET birth_state = 'Other'
WHERE birth_country NE 'USA';
RUN;
PROC SQL;
SELECT *
FROM born93
WHERE birth_state = 'Other';
RUN;
DELETE¶
This allows you to delete columns, meeting a specified criteria, from a table.
DELETE FROM table
WHERE condition;PROC SQL;
DELETE FROM born93
WHERE name_last LIKE "%egh";
RUN;
* query number of rows;
PROC SQL;
SELECT COUNT(*)
FROM born93;
RUN;
Table Joins with SQL¶
There are several types of joins, some of which can be visualized below:
Inline-style:
- Inner join: returns only records in both tables
- Outer join: returns all records in both tables
- Left/Right (outer) join: returns all records in one table and matching records in other table
Join Syntax¶
SELECT l.var1, l.var2, r.var3, ...
FROM lefttab as l
INNER JOIN righttab as r
ON l.key=r.key;Other options for the join argument are:
LEFT JOINRIGHT JOINFULL JOIN
Inner Join¶
Q: Sketch out a SQL command to conduct an inner join on born93 and batting.
PROC SQL;
SELECT *
FROM born93 as b
INNER JOIN STAT408.batting as s
ON b.player_id = s.player_id;
RUN;
Left Join¶
Now conduct a query that is a left join on born93 (as the left table) with batting as the right table.
PROC SQL;
SELECT *
FROM born93 as b
LEFT JOIN STAT408.batting as s
ON b.player_id = s.player_id;
RUN;- Q: does the query have more, the same, or less records than the previous query?
- Q: how would the results change if batting was the left table?
PROC SQL;
SELECT *
FROM born93 as b
LEFT JOIN STAT408.batting as s
ON b.player_id = s.player_id;
RUN;
SAS Macros¶
SAS Macro code consists of two components:
- macro variables and
- macros.
Macro variables¶
Macro variables is a single variable that does not belong to a data set and the value is always the character. Names of macro variables are begin with an ampersand, such as ¯ovar.
Macros¶
A macro is similar to an R function, but still bundles SAS DATA and PROC statements. Marco names start with a percent sign, such as %compile.
Macro Variables¶
Macro variables are particularly useful in industrial settings where procedures are run every week or month. Rather than modifying all of the code, a macro variable can be used to adjust the week or month.
Defining Macro Variables¶
Macro variables are defined with the following syntax
%LET year = 1993;Q: Now write code to query the player data set to extract the count of players by birth year.
%LET year = 1993;
TITLE "Number of MLB players born in &year";
PROC SQL;
SELECT COUNT(*)
FROM STAT408.player
WHERE birth_year = &year;
RUN;
TITLE;
%LET year = 1983;
TITLE "Number of MLB players born in &year";
PROC SQL;
SELECT COUNT(*)
FROM STAT408.player
WHERE birth_year = &year;
RUN;
TITLE;
Macro Procedures¶
Similar to the last example, and functions in R, SAS macros can be used to streamline code for repeated procedures. As with R, debugging inside a macro environment can be more complicated. So make sure to that the code inserted in the macro statement is working before applying the macro commands.
The Little SAS Book describes macros as a sandwich,
%MACRO macro_name (parameter1= , ...);
macro_text
%MEND macro_name;where the %MACRO and %MEND statements are the bread. After being defined, the macro can then be run using the following statement %macro_name(val1); (if one parameter is defined).
Q: Now write a macro to print the number baseball players born in the years 1983, 1990, and 1993.
%MACRO yearborn (year =);
libname STAT408 '/folders/myfolders/';
TITLE "Number of MLB players born in &year";
PROC SQL;
SELECT COUNT(*)
FROM STAT408.player
WHERE birth_year = &year;
RUN;
TITLE;
%MEND yearborn;
%yearborn(year = 1983);
%yearborn(year = 1990);
%yearborn(year = 1993);
Macros with Conditional Logic¶
Conditional logic can also be used inside a macro statements. For instance continuing the previous example, if we also want to include an option to create a table with the players names this can be done with conditional logic.
%MACRO yearborn (year =, create=);
libname STAT408 '/folders/myfolders/';
TITLE "Number of MLB players born in &year";
PROC SQL;
SELECT COUNT(*)
FROM STAT408.player
WHERE birth_year = &year;
RUN;
TITLE;
%IF &create = 'YES' %THEN %DO;
PROC SQL;
CREATE TABLE born&year as
SELECT *
FROM STAT408.player
WHERE birth_year = &year;
RUN;
%END;
%MEND yearborn;
%yearborn(year = 1995, create= 'YES');
%yearborn(year = 1994, create = 'NO');
PROC PRINT DATA=born1994;
RUN;
PROC PRINT DATA=born1995;
RUN;
A brief overview of SAS PROC IML¶
IML stands for Interactive Matrix Language and provides some matrix algebra capacity. http://www.yorku.ca/pek/index_files/quickstart/IMLQuickStart.pdf
We are not going to cover it in detail, I only want you to know that it does exist.
proc iml;
a = I(3); * 3x3 identity matrix;
b = j(4,4,1); *4x4 matrix of 1's;
c=diag({1 2 4});
d=({1 2, 3 4});
print a; print b; print c; print d;
quit;
proc iml;
X ={1 2, 3 4};
Y ={-4 3,-2 -1};
XY = X*Y; *matrix multiplication;
tX = t(X); *transpose using function;
DX = det(X); *matrix determinant;
X_inv = inv(X); *matrix inverse;
X_eigval = eigval(X); *eigen values of matrix;
X_eigvec = eigvec(X); *eigen vector of matrix;
print X; print Y; print XY; print tX; print DX; print X_inv; print X_eigval; print X_eigvec; quit;