Proc SQL [3-14]

Creating New Columns
SELECT 800 AS tuition
FROM school;

​The DURATION variable contains the trip duration in seconds.

In the last two sections, we have learned how to select existing columns from a SAS table.
​
New columns can also be created in the SELECT clause.

Copy and run the SCHOOL table from the yellow box below if you haven't done so.

​
Creating Column(s) with Fixed Value

Let's assume the tuition fee for each student is $800.

With Proc SQL, we can create a new column to display the tuition fee for each student.
Proc sql;
select name, height,
  800 as tuition
from school;
quit;

The tuition fee of $800 is displayed in the TUITION column:

Creating Column(s) with Computed Values

You can also create a new column that contains computed values instead of fixed values. 

Example

The HEIGHT column in the SCHOOL table contains the height of students in inches.

​Now, let's create a new column containing the HEIGHT in centimeters.
Proc sql;
select name, height,
  height*2.54 as hcm
from school;
quit;
Note: 1 inch = 2.54 centimeters.

The height in centimeters is computed as height (in inches) x 2.54. 

It is displayed in the HCM column:

Format and Label

The new height column is displayed as 'hcm' and the height is displayed to three decimal places.​

This doesn't look great. We can add a proper format and label to the column to enhance the output.

Example
Proc sql;
select name, height,
  height*2.54 as hcm
  format=5.1
  label="Height (cm)"
from school;
quit;

The (5.1) format and the "Height (cm)" label are assigned to the HCM column. 

The output now displays the proper label and format:
Exercise

Locate the CARS table from the SASHelp library. 

The commission for each car sold is 2% of the MSRP.

Write a Proc SQL step to display the commission along with the make, model and MSRP of each car.

Label the COMMISSION column as "Commission (2% of MSRP)". 
Need some help? 


HINT:

The commission column can be computed as MSRP * 0.02.


SOLUTION:

Proc sql;
select make, model, msrp,
msrp*0.02 as commission
label="Commission (2% of MSRP)"
from sashelp.cars;
quit;

Fill out my online form.