Sentry Page Protection
Proc SQL [3-14]
Creating New Columns
SELECT 800 AS tuition
FROM school;
FROM school;
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;
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;
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:
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;
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)".
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.