Search the site...

SASCRUNCH TRAINING
  • Home
  • Member's Area
  • How to Start
  • SAS Interface
  • Creating a Data Set
  • Practical SAS Training Course
  • SAS Certified Specialist Training Program
  • Proc SQL Course
  • Introduction to Time Series Analysis
  • SAS Project Training Course
  • Full Training / Membership
  • Sign up
  • About us
  • Contact us
  • Home
  • Member's Area
  • How to Start
  • SAS Interface
  • Creating a Data Set
  • Practical SAS Training Course
  • SAS Certified Specialist Training Program
  • Proc SQL Course
  • Introduction to Time Series Analysis
  • SAS Project Training Course
  • Full Training / Membership
  • Sign up
  • About us
  • Contact us
Proc SQL [3-14]


Creating New Columns
SELECT 800 AS tuition
FROM school;

​The DURATION variable contains the trip duration in seconds.
Picture

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.
Picture
Proc sql;
select name, height,
  
800 as tuition
from school;
quit;

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


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

​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:
Picture


Format and Label

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

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:
Picture

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)". 
Next

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.
Already a member? Go to member's area.