32.2 - Using the SELECT Clause

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