SQL Advanced Queries (SAS/SQL)

There are many useful SQL Queries that can be utilized within The SAS Enhanced Editor. Below are examples and descriptions of query types that I found to be particularly useful.

/* Selects multiples variables from the same data set and creates a new variable within the query results. This variable reflects the sum of all VARD observation values which satisfy the specifications of the WHERE clause. The query is ordered by the observation values of VARB. */

PROC SQL;
SELECT VARA, VARB, VARC, sum(VARD) as VARDSUM
FROM TABLEA
WHERE VARA >500
ORDER BY VARB
;
QUIT;

/****************************************************************************/

/* Selects multiple variables from the same data set and creates a new variable within the query results. This variable reflects the average of all VARD observation values which satisfy the specifications of the WHERE clause. The query is ordered the by the observation values of VARB. */

PROC SQL;
SELECT VARA, VARB, VARC, avg(VARD) as VARDSUM
FROM TABLEA
WHERE VARA >500
ORDER BY VARB
;
QUIT;

/****************************************************************************/

/* Selects multiple variables from the same data set and creates a new variable within the query results. This variable is comprised of the product value of VARD observation values multiplied by .5. These values must satisfy the specifications of the WHERE clause. The query is ordered by the observation values of VARB. */

PROC SQL;
SELECT VARA, VARB, VARC, VARD * .5 as VARDPRODUCT
FROM TABLEA
WHERE VARA >500
ORDER BY VARB
;
QUIT;

/****************************************************************************/

/* Selects multiple variables from the same data set. Only displays results which have a VARA value greater than 500. Order the data by VARB in descending order.*/

PROC SQL;
SELECT VARA, VARB, VARC, VARD
FROM TABLEA
WHERE VARA >500
ORDER BY VARB DESC
;
QUIT;

/****************************************************************************/

/* Select multiple variables from the same data set. Only display results which have a VARA value greater than 500. Order the results first by VARB and then by VARA.*/

PROC SQL;
SELECT VARA, VARB, VARC, VARD
FROM TABLEA
WHERE VARA >500
ORDER BY VARB VARA
;
QUIT;

/****************************************************************************/

/* Selects multiple variables from multiple data sets. Only display results which have a VARA value greater than 500. Order the results by VAR2.*/

PROC SQL;
SELECT TABLEA.VARA, TABLEA.VARB, TABLEA.VARC, TABLEB.VAR1, TABLEB.VAR2
FROM TABLEA, TABLEB
WHERE VARA >500
ORDER BY VAR2
;
QUIT;

/****************************************************************************/

/* Selects results only from a range that occurs between two limits. These limits will also be included in the query results. This particular operator is useful when querying  by date values. */

PROC SQL;
SELECT VARA
FROM TABLEA
WHERE VARA between 800 and 900
;
QUIT;

/****************************************************************************/

/* Selects results from a group of variable observations that contain certain string. These limits will also be included in the query results. */

PROC SQL;
SELECT VARA
FROM TABLEA
WHERE VARA contains ‘SAS’
;
QUIT;

/****************************************************************************/

/* Selects results from a group of variable observations that potentially match a selection of varying values. */

PROC SQL;
SELECT VARA
FROM TABLEA
WHERE VARA in (‘SPADE’, ‘DIAMOND’, CLUB’)
;
QUIT;

/****************************************************************************/

/* Selects results from a group of variable observations that potentially match. However, in this case, wildcard characters are utilized to specify potential matches. */

PROC SQL;
SELECT VARA
FROM TABLEA
WHERE VARA LIKE ’S_ADE’
;
QUIT;

/****************************************************************************/

/* Select results from a group of variable observations that potentially match against another value. However, in this case, wildcard characters are utilized to specify potential matches. This differs from the previous example as the ‘%’ character is used to act as a match against entire words */

PROC SQL;
SELECT VARA
FROM TABLEA
WHERE VAR LIKE ’% Jones’ /*This would return all individuals in a query with the last name of Jones */
;
QUIT;

/****************************************************************************/

/* When sorting by a variable that was defined within the submitted query, the keyword CALCULATED must be utilized. */

PROC SQL;
SELECT VARA, VARB, VARC, VARD * .5 as VARDPRODUCT
FROM TABLEA
WHERE CALCULATED VARD < 500
ORDER BY VARB
;
QUIT;

/****************************************************************************/

/* If you want to specify a title for a query that will display in the output, utilize the title1 statement. However, be aware that this title will be utilized as a title for every subsequent query unless a blank title is provided prior to the execution of the next query.*/

Title1 ‘This is a test query’;
PROC SQL;
SELECT VARA, VARB, VARC, VARD * .5 as VARDPRODUCT
FROM TABLEA
WHERE CALCULATED VARD < 500
ORDER BY VARB
;
QUIT;
Title1 ‘ ‘; /*This prevents the title from re-displaying on subsequent query results */

/****************************************************************************/

/* GROUP BY allows you to group by the observation types specified within the variable column specified.*/

PROC SQL;
SELECT VARA, VARB, VARC
FROM TABLEA
WHERE CALCULATED VARD < 500
GROUP BY VARA
;
QUIT;

/****************************************************************************/

/* Stacks two data sets vertically. */

PROC SQL;
SELECT *
FROM TABLEA
UNION ALL
SELECT *
FROM TABLEB
;
QUIT;

/****************************************************************************/

/* Formats a newly created variable displayed within the query results. */

PROC SQL;
SELECT VARA, VARB, VARC, VARD * .5 as VARDPRODUCT format=best32.
FROM TABLEA
WHERE CALCULATED VARD < 500
ORDER BY VARB
;
QUIT;