Advanced Topic: PROC SQL

Question 1

What can PROC SQL be used for?

Answer 1

• Retrive data from and manipulate SAS tables
• Add/modify data values in table, drop columns
• Create tables
• Join tables together
• Generate reports

Question 2

What is the general syntax for PROC SQL?

Answer 2

proc sql;
select columns
from table
(where expression)
(group by columns)
(having)
(order by columns);
quit;

Note: this order cannot be changed in SQL.

Question 3

In SQL, what does the commands select, from, where, group by, having, and order by do?

Answer 3

Select: specifies the columns to be selected
From: specifies tables to be queried
Where: subsets the data based on conditions
Group by: classifies data into groups by specified columns
Having: works with group by to restrict groups displayed
Order by: sorts the rows by the values.

Question 4

T/F: You need the run statement in PROC SQL.

Answer 4

False: run has no effect. PROC SQL statements executed immediately. To end, must use quit statement.

Question 5

Given the following code, explain what each line is doing.

1) proc sql;
2) select empid, jobcode, salary, salary*0.06 as bonus
3) from payrollmaster
4) where salary lt 32000
5) order by 2, empid, bonus
6) quit;

Answer 5

1) calls PROC SQL
2) selects columns, separated by commas, and creates new column of bonus
3) gets data from the dataset called payrollmaster
4) selects only rows/obs with salary < 32,000
5) Order first by 2nd column(jobcode), then empid, and finally bonus
6) quits PROC SQL

Question 6

Where do you put semicolons in PROC SQL?

Answer 6

Only after the last clause in the statement.

Question 7

T/F: columns created in PROC SQL are automatically permanent.

Answer 7

False: New columns exist only for the duration of the query, unless a table/view is created.

Question 8

T/F: The ORDER BY clause sorts rows in ascending order.

Answer 8

True: if you want descending, need to specify with DESC

Question 9

What do you use if you want all columns to be referenced?

Answer 9

*

Question 10

List some functions you can use in PROC SQL.

Answer 10

min, max, sum, avg, nmiss, n, mmiss, std, var, freq

Question 11

Write the general code to create a new table in proc SQL.

Answer 11

proc sql;
create table tablename as
select columns
from dataset
group by columns;
quit;

Question 12

T/F: When the create table statement is used, the query creates a report in the results window.

Answer 12

False: It does not, instead the new table/dataset is created and results are not printed automatically.

Question 13

Describe the 4 types of joins.

Answer 13

1) Outer: keeps all observations from both datasets
2) Inner: keeps only the observations that match
3) Right: keeps all observations from the right side (second defined table) whether they match or do not match left side.
4) Left: keeps all observations from the left side (first defined table) whether they match or do not match right side.

Note: think of a venn diagram. outer = all, inner = overlaping part, right = all of right circle, left = all of left circle

Question 14

Describe what is happening in each line of the following code.

proc sql;
1) create table payrollmaster as
2) select payrollmaster.empid, lastname, jobcode, salary, newsals.newsalary
3) from payrollmaster, newsals
4) where payrollmaster.empid = newsals.id
5) order by empid;
quit;

Answer 14

1) Creating a new table/dataset called payrollmaster
2) Selecting columns from both tables defined in from statement
3) Selecting payrollmaster and newsals dataset
4) Joining data on common key of id
5) Ordering data by empid

Question 15

T/F: You need to sort the data before joining in PROC SQL.

Answer 15

False: does not need to. SQL can combine without sorting

Question 16

T/F: The variable used for combining the data set does not have to have the same name in both files.

Answer 16

True: Because of how we combine files. Need to specify both the dataset and variable to combine.

Ex. dataset.variable = payrollmaster.empid