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;

Output

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 the 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 a 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 in the previous example. The added code is to close the current listing output and open the pdf destination and save the pdf output generated by the SQL procedure that follows the specified file name. The end part of ODS statements tells SAS to close the pdf destination and open the 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 the 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 the 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.

167  PROC SQL;
168      CREATE TABLE SAT_Scores as
169      select ID,
170          SATM,
171          SATV
172      from stat482.survey;
ERROR: Libref STAT482 is not assigned.
173  QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

In this example, table SAT_scores has 226 rows and 3 columns. 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.

1. 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, and ORDER BY. But the SELECT and FROM clauses are essential and indispensable. Other clauses are optional. All clauses have to be written in the order listed in the syntax. For each SELECT statement, only one semicolon is needed at the end of the statement.
2. 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.

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;

Output

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 have 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 the DATA step to create new variables.

Use the following program 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;

Output

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 weekends_traffic_volume.

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 the SELECT statement so you can enhance the appearance of the query output.

The following program adds the format to dates, labels columns, and adds 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;

Output