*	        CODE: SAS PROC SQL Tutorial - Part 1: Extracting Data
*	      AUTHOR: Blink 7 (http://www.blink7.com)
*	        DATE: April 12, 2009
*	REQUIREMENTS: -
*	 DESCRIPTION: Simple SQL Queries to Retrieve Data
*	       NOTES: 1) You may have to change the LIBNAME file locations, depending
*				  	 on where your data sets are stored
*				  2) The TITLE commands have been included to labels the results, since we are not
*					 redirecting output to a new data set
;

/* Library statement for project data */
LIBNAME store 'Z:\_Web\Blink7v4\_Content\Files\Tutorial010';


/* Read an entire data set and output to the screen */
TITLE1 'List of Store Employees';
PROC SQL;
	SELECT	*
	FROM 	store.staff
	;
QUIT;
TITLE;

/* Read specific variables from a data set */
TITLE1 'First and Last Names of Store Employees';
PROC SQL;
	SELECT	first_name,
			last_name
	FROM 	store.staff
	;
QUIT;
TITLE;

/* Filter data results*/
TITLE1 'Store Employees with the Surname Wilson';
PROC SQL;
	SELECT	*
	FROM 	store.staff
	WHERE	last_name = 'Wilson'
	;
QUIT;
TITLE;

/* Filter data results by matching on multiple variables*/
TITLE1 'Store Employees in the Media station with the Surname Wilson';
PROC SQL;
	SELECT	*
	FROM 	store.staff
	WHERE	last_name = 'Wilson'
			AND station = 'Media'
	;
QUIT;
TITLE;

/* Filter data results using negative matching*/
TITLE1 'Store Employees that do NOT have the Surname Wilson';
PROC SQL;
	SELECT	*
	FROM 	store.staff
	WHERE	last_name ^= 'Wilson'
	;
QUIT;
TITLE;

/* Filter data results using a list of values*/
TITLE1 'Store Employees in the Computer or Media station';
PROC SQL;
	SELECT	*
	FROM 	store.staff
	WHERE	station IN ('Computers','Media')
	;
QUIT;
TITLE;

/* Read an entire data set and output to a new data set */
PROC SQL;
	CREATE TABLE staff_copy AS
	SELECT	*
	FROM 	store.staff
	;
QUIT;