The INSERT statement is used primarily in three instances. One is when you wish to enter a single row into a dataset. The second is when you wish to load the results of a SELECT statement all at once into a table. (In some systems, such as Access, you can actually create a table using an INSERT statement).
So far we’ve spent our time discovering how SQL can be used to aggregate, sort, and manipulate data for analysis and presentation purposes. But everything we’ve done is under the assumption the data was already available. In this lesson we’ll learn about the other part of SQL, architecture!
SQL Architecture consists of creating, deleting (dropping), and altering tables, views, and indexes. While most DBMSs will come with an out-of-the-box SQL administration tool (such as SQL Server Management Studio), SAS does not. SAS does allow you to create a table manually using the GUI, however it is slow and cumbersome, so we’ll focus on the actual SQL syntax.
The CASE expression is essentially the SQL version of “If-Then-Else”. A CASE expression allows you to return a result that is conditionally evaluated for each row in your table. Instead of using If [true] Then [NewValue], use the WHEN-THEN clauses when you want to execute a CASE expression that meet the criteria. The Else statement is optional but highly suggested.
The CASE expression can be executed in two manners. One is be evaluating the operand following the CASE keyword, which will then require you to account for each possible result, the other is to use the operand in each WHEN expression.
Up to this point, we’ve only used single-table queries. If the only analysis we ever did were on single-table databases, the queries we’ve learned so far would be all we would need! However, analysis is never that easy. Most production databases will be normalized in a snowflake schema, which require lengthy joins, and even datamarts using star schemas will require multiple table queries.
So, how does an analyst cope? With Joins. SQL utilizes seven joins, six distinct, which allow us to combine multiple input-tables into a single output-table.
In SQL, data aggregations are done using one or more aggregation functions and sometimes the GROUP BY statement coupled with the HAVING statement. Using these functions, we can return data in a more usable format for reporting and analysis purposes. The HAVING statement allows us to constrain the results of an aggregation, similar to a WHERE statement.
As with most every computer programming language, SQL supports the use of functions to manipulate data. There are two types of functions in SQL; Those that produce strings and those that produce numbers. However, it’s important to realize that functions in SAS SQL can be different than functions in other database systems, so please be careful when trying to use queries in other systems.
As with all SAS procedures, the WHERE statement allows you to constrain the results of a procedure based on one or more conditional logic statements. Every row that meats the criteria set forth in the statement will be in the resulting dataset. Multiple constraining statements can be grouped together using Boolean operators (and/or), as shown in the DeMorgan’s Laws section.
Since we are using the SAS version of SQL, every SQL statement must be surrounded by the following SAS statements:
Proc SQL;
[SQL Statements];
Quit;
As you can see, line one tells SAS that we are going to be performing a SQL query, and as such, it should expect SQL statements until it reaches the Quit directive (line 3). As mentioned in the previous chapter, every SQL statement must be terminated with a semicolon, just like SAS statements.
SQL, or Structured Query Language, is a near universal database language and is used by anyone and everyone who works with databases. SQL is designed for management and manipulation of data in a structured environment, usually a relational database, such as SQL Server. Traditionally SQL is used for CRUD applications (Create, Read, Update, and Delete) but can also do primitive mathematical functions. At its roots, SQL is designed specifically to query relational data.
SAS (nee Statistical Analysis Software) is an application that uses non-compiled procedural programs (SAS programs) composed of a series of written instructions, called statements. As with all computer programming languages, the programs written are only as smart as the user allows them to be. In a SAS program, the user communicates what it would like SAS to perform by keying in statements and then executes them one at a time or in entirety.
(more…)
Welcome to Analysis: 