SAS SQL

[Ch.7] PROC SQL View Advantages (6)

1) Avoid storing copies of large tables 2) Allows access to most recent data 3) Combine data from multiple tables and libraries 4) Simplify complex queries 5) prevent other users from altering query code 6) prevent other users from seeing data they shouldn't

[Ch.7] Creating Tables - 3 Methods

1) Copy columns and rows from existing table(s) 2) Copy only the column structure from an existing table and add rows later 3) Define new columns and create an empty table Always use CREATE TABLE keywords CREATE TABLE statement creates one table per statement

[Ch.7] PROC SQL View Disadvantages

1) Might produce different results each time they are accessed 2) can require significant resources

[Ch.7] Specifying an Ordered list of Values in the VALUES clause

INSERT INTO table name (optional column list separated by commas) VALUES (value,value,...) Values must be in VALUES clause in order of corresponding columns in the table option column clause can specify a different order of values

[Ch.4] Abbreviating the Code with a Table Alias

Qualified column names can be long Can assign an alias to a table to make column names shorter Assign table aliases int e FROM clause FROM Customers as C, transactions as t WHERE c.ID=t.ID; Table aliases optional unless you are joining a table to itself

[Ch.3] Using Remerged Summary Statistics

Use WHERE clause to subset rows you need from table Can use a function in a calculation for each row if needed {Salary/sum(Salary)}

[Ch.5] Using a Subquery that Returns Multiple Values

- Inner subquery evaluated first and creates a list of values -LIst passed to outer query

[Ch.1] PROC SQL

- SAS procedure to query data sets - Can use in place of data step with less code - data set = table - observation = row - variable = column - a language that describes WHAT you want to do but not how to do it - picks the most efficient processing order

[Ch.1] Using a SELECT Statement

- retrieves data from one or more tables - creates a report by default - made up of clauses - can contain a combination of 6 clauses SELECT item, item,* FROM* WHERE GROUP BY HAVING ORDER BY - each clause does NOT end in semicolon - just one at end - order of column names in select clause determine order they appear in the report - clauses must appear in this order - each SELECT statement generates a report - items in SELECT statement separated by commas

[Ch.5] Types of Subqueries (2)

1) Noncorrelated Subquery - Self contained - Executes independently from outer query - Can run on its own as a standalone query - Allows you to build and test code in pieces 2) Correlated Subquery - Dependent on the outer query - Requires one or more values to be passed to it from the outer query before it can be resolved - tend to use more resources than uncorrelated

[Ch.7] Adding Data into a Table (3 Ways)

1) Specify column name value pairs - values part of code 2) Specify an ordered list of values - values part of code 3) Specify a query that returns rows - use when data exists in an existing table All use INSERT INTO statement INSERT INTO table name.... Can add data to only one table

[Ch.1] What order must the clauses in the SELECT statement appear?

1. SELECT clause 2. FROM clause 3. WHERE clause 4. GROUP BY clause 5. HAVING clause 6. ORDER BY clause

[Ch.1] How many reports does this PROC SQL step generate? proc sql; select Employee_ID, Employee_Gender, Salary from orion.employee_payroll; select Job_Title, Salary from orion.staff; quit;

2

[Ch.1] How many statements are in this program? proc sql feedback; select * from acme.payroll where salary<750000; describe table acme.staff; quit;

4

[Ch.1] Which of these statements is correct? a. The SELECT clause specifies the columns to include in output, the FROM clause specifies the data source(s), and the WHERE clause selects a subset of individual rows to be processed. b. The SELECT specifies the data source(s), the FROM clause categorizes the output rows, and the WHERE clause selects a subset of groups of rows. c. The SELECT clause selects a subset of groups of rows, the FROM clause selects a subset of individual rows to be processed, and the WHERE clause sorts the output rows.

A

[Ch.4] Does a self-join require reading data from different rows of the same table? a. yes b. no

A A self-join, also known as a reflexive join, is a query in which a table is joined to itself.

[Ch.3] Using Summary Functions

Add aggregate or summary functions in SELECT clause with alias to create a summary column Number of arguments you specify determines whether you sum across a row or down a column Only SAS sum functions accepts multiple arguments ANSI can't sum across rows

[Ch.7] Suppose you have been asked to write a program for a specific user so that she can generate reports based on the most up-to-date data from multiple tables. This user should not have access to some of the information in the source tables. What is the best way to access the data in this situation? a. by using a view b. by accessing the source tables directly c. either way - it doesn't matter

A A view retrieves the most current data from the underlying tables when the view is executed. Also, by using a view, you can make sure that the user sees only the appropriate data from the underlying tables.

[Ch.4] Given these two select statements, will the result sets contain the same data? select * from customers left join transactions on customers.ID=transactions.ID; select * from transactions right join customers on customers.ID=transactions.ID; a. yes b. no

A Because the tables are reversed in each join, the actual data in the results sets will be the same.

[Ch.2] Which of the following CASE expressions is correct? a. case when 5<=years_experience<=10 then 'experienced' when years_experience>10 then 'advanced' else 'entry' end as Level b. case else 'entry' when 5<=years_experience<=10 'experienced' when years_experience>10 'advanced' end as Level c. case when 5<=years_experience<=10 then 'experienced' when years_experience>10 then 'advanced' else 'entry'

A Begin with the keyword CASE, follow with the WHEN-THEN clauses, and finish with the keyword END.

[Ch.7] When you are writing a program that generates reports, it is best to reference a view instead of a table in which of the following situations? a. when you need to ensure that you are accessing the most current data b. when you want to minimize CPU and memory usage c. when your program accesses the same data source multiple times d. when the SAS library contains a view and a table that have the same name

A If a view is based on the original copy of a table, referencing the view ensures that you are accessing the most current data. However, if the same program accesses the same data source multiple times, it is probably better to reference a table, to ensure that the same data is returned each time. Views actually use more CPU and memory resources than tables, although they do save disk storage space. A SAS library cannot contain a view and a table that have the same name.

[Ch.2] Which SELECT statement selects rows from the retail.orders table in which the total retail price is $500 or more? partial table retail.orders OrderID Retail_Price 1230058123 $16.50 1230080101 $247.50 1230106883 $28.30 1230147441 $32.00 a. select OrderID, Retail_Price from retail.orders where Retail_Price ge 500; b. select OrderID, Retail_Price from retail.orders where Retail_Price ge $500; c. select OrderID, Retail_Price from retail.orders where Retail_Price ge $500.00;

A In the WHERE clause, you always specify numeric values as unformatted values.

[Ch.4] Which of the following is true of a DATA step merge? a. requires the tables to be indexed or sorted b. can specify join keys that have different names c. can join on an inequality d. returns all possible combinations of matching rows

A Most of these statements are true of SQL joins but not DATA step merges.

[Ch.2] What information does this program write to the SAS log? proc sql; describe table acme.payroll; quit; a. a list of columns in the table and their attributes b. a list of qualified column names for the columns in the table c. all of the columns and rows in the table

A The DESCRIBE TABLE statement generates a PROC SQL definition of the table in the SAS log. The definition includes a list of columns in the table and their attributes.

[Ch.3] Which syntax will select Job_Title values with a total Bonus value greater than $10,000? a. select Job_Title, sum(salary*0.1) as Bonus from orion.employee_information group by Job_Title having Bonus > 10000; b. select Job_Title, sum(salary*0.1) as Bonus from orion.employee_information where Bonus > 10000 group by Job_Title; c. both of the above d. neither of the above

A The HAVING clause can refer to a calculated column alias.

[Ch.4] Which of the following queries generates a result set that includes both matching and nonmatching rows? a. select phones.Employee_ID, Employee_Name, Phone_Number from company.phones, company.addresses; b. select phones.Employee_ID, Employee_Name, Phone_Number from company.phones, company.addresses where phones.Employee_ID = addresses.Employee_ID; c. all of the above d. none of the above

A The query that generates a Cartesian product includes both matching rows and nonmatching rows in the result set. The inner join produces a result set that contains only matching rows.

[Ch.4] Which clause can you add to the following query so that it performs an inner join? select * from orion.customer, orion.order_fact; a. where customer.Customer_ID= order_fact.Customer_ID b. on customer.Customer_ID= order_fact.Customer_ID c. by Customer_ID d. None. This query already performs an inner join.

A The query, as shown, creates a Cartesian product. To perform an inner join, you add a WHERE clause that specifies join conditions. You cannot use the ON clause unless you use the alternate syntax for an inner join. You use the BY statement in the DATA step, not in PROC SQL.

[Ch.4] How many syntax errors does the following join have? select r.Name, Course, Grant from school.registration, right outer join school.scholarships on r.Name=s.Name where Grant > 0; a. more than one error b. one error c. no errors

A This query has more than one error. The SELECT clause and the ON clause use table aliases in qualified column names, but the FROM clause does not define the table aliases. In the FROM clause, a comma should not appear after the first table name. Also, the keywords in the FROM clause should be RIGHT JOIN.

[Ch.2] In a SELECT clause, which of the following keywords conform to the ANSI standard for SQL? a. DISTINCT b. UNIQUE

A Using DISTINCT to select unique values of one or more columns conforms to the ANSI standard. However, using UNIQUE does not conform to the ANSI standard.

[Ch.4] Outer Joins - 3 Types

Allows you to return nonmatching rows 1) Full Outer Join: returns all matching and nonmatching rows from BOTH tables 2) Left Outer Join: returns all matching rows plus nonmatching rows from the first table 3) Right Outer Join: returns all matching rows plus nonmatching rows from the second table

[Ch.6] Which statement is true about using the CORR modifier in a set operation? a. When used with the EXCEPT operator, the CORR modifier causes PROC SQL to align columns by name. Columns that have different names in the intermediate result sets are eliminated from the final result set. b. When used with the INTERSECT operator, the CORR modifier causes PROC SQL to overlay columns by position in the final result set. c. When used with the OUTER UNION operator, the CORR modifier causes PROC SQL to eliminate columns that have different names from the final result set.

A When the CORR modifier is used with EXCEPT and INTERSECT, it causes PROC SQL to align columns that the intermediate result sets have in common. When CORR is used with the OUTER UNION operator, PROC SQL aligns columns that have the same name and also includes columns that do not have the same name in both intermediate result sets.

[Ch.1] For which of the following PROC SQL statements can you use the VALIDATE statement to check the syntax? Select all that apply. A) select Customer_ID from orion.qtr1_2007 intersect select Customer_ID from orion.qtr2_2007; B) describe orion.qtr1_2007; C) select Employee_Name, City, Birth_Date from orion.employee_payroll as p, orion.employee_addresses as a where p.Employee_ID=a.Employee_ID; D) alter table work.employee_payroll modify Phone_Type label='Work, Home, Cell';

A & C You can use the VALIDATE statement with any SELECT statement, but not with other statements.

[Ch.6] By default, which of the following set operators remove(s) duplicate rows from the final result set? Select all that apply. a. EXCEPT b. INTERSECT c. OUTER UNION d. UNION

A, B, D

[Ch.3] Which of the following options correctly describe how to construct a PROC SQL statement? Select all that apply. a. You specify the keywords PROC SQL, the option or options, and end with a semicolon. b. You can specify only one option in one PROC SQL statement. c. You must specify options in the PROC SQL statement in a specific order. d. If multiple queries are specified, PROC SQL applies the options to all of the queries in the step.

A, D

[Ch.3] Which of the following summary functions calculate statistics down the column? Select all that apply. a. avg(Salary) b. sum(Cost_Price,Sales_Price) c. max(Cost_Price,Sales_Price,Factor) d. min(Dependents)

A, D

[Ch.3] Which of the following options use the ANSI standard? Select all that apply. a. 'Patient Identifier' b. Patient ID c. Label='Patient Followup' d. 'Patient Followup Date'

A, D You specify text, up to 256 characters, and enclose it in quotation marks to use an ANSI standard label.

[Ch.6] Using the UNION Operator with ALL Modifier

ALL modifier can suppress removing duplicate rows Use when duplicate rows not possible to speed up processing

[Ch.6] Modifying Default BEhavior of Set Operators

ALL modifies default behavior for rows; final results include all rows instead of just unique CORR modifies default behavior for columns; aligns columns not by position but by ones that have the same name; suppresses columns with names that are not in both

[Ch.6] Using the INTERSECT Operator with Modifiers

ALL prevents duplicate rows from being removed CORR aligns columns by name instead of position - used when matching columns are not in the same position

[Ch.6] Using the EXCEPT Operator with Modifiers

ALL prevents duplicate rows from being removed CORR aligns columns by name instead of position and columns without same name are removed from final set

[Ch.1] Which PROC SQL program uses VALIDATE correctly? a. proc sql; select Customer_ID, Country, Customer_Name from orion.customer; validate quit; b. proc sql; validate select Customer_ID, Country, Customer_Name from orion.customer; quit; c. proc sql validate; select Customer_ID, Country, Customer_Name from orion.customer; quit;

B

[Ch.2] Which date function would you use to create the Birth_Month column for the following rows from the employee_information table? INSERT REPORT OUTPUT HERE a. TABLE() b. MONTH(argument) c. INT(argument) d. None of the above

B

[Ch.5] A subquery can reside in which of the following clauses? Select one: a. SELECT or WHERE b. WHERE or HAVING c. SELECT or FROM d. WHERE or FROM

B

[Ch.5] An in-line view is nested in the WHERE clause. Select one: a. Yes b. No

B

[Ch.5] A subquery can return several rows of data, but can a subquery return values from multiple columns? Select one: a. Yes b. No

B

[Ch.5] Which statement is true regarding a noncorrelated subquery? Select one: a. The subquery must be enclosed in brackets. b. The subquery can run as a stand-alone query. c. The subquery is known as a virtual table. d. The outer query passes a value to the inner query before it can execute.

B

[Ch.2] Which expression is valid in the SELECT clause? a. Bonus=Salary*.03 b. Salary *.01 as Increase c. Scan=(Job_Title,1,' ') d. Salary*.03 Bonus

B An = is not allowed in the expression in the SELECT clause when creating a new column. To assign a name that is referred to as an alias in PROC SQL, use the keyword AS followed by a valid SAS name.

[Ch.2] Suppose the table acme.payroll stores a label for each column in addition to the column name. When you run this program, what information appears in the column headings in the report? proc sql; select * from acme.payroll where salary<750000; quit; a. column names b. column labels c. numbers that indicate the sequence in which the column is stored

B If the columns in an input table have permanent labels, a report produced by the query displays the labels instead of the names.

[Ch.2] In orion.sales, the columns appear in this order: Employee_ID, First_Name, Last_Name, Gender, Salary, Job_Title, Country, Birth_Date, Hire_Date. When you run the following query, in what order do columns appear in the report? proc sql; select Last_Name, First_Name from orion.sales where Job_Title='Sales Rep. I'; quit; a. First_Name, Last_Name b. Last_Name, First_Name c. Last_Name, First_Name, Job_Title

B In a query's output, the columns appear in the order in which they are listed in the SELECT statement. Columns that are listed in other clauses, but not the SELECT statement, do not appear in output.

[Ch.2] Which of the following SELECT statements correctly selects all rows in which one or both of the conditions are true? a. select Employee_ID from acme.employees where Job_Title contains 'Sales' and Gender='M'; b. select Employee_ID from acme.employees where Job_Title contains 'Sales' or Gender='M'; c. select Employee_ID from acme.employees where Job_Title contains 'Sales' and where Gender='M';

B Only one WHERE clause, and one instance of the keyword WHERE, can appear in a SELECT statement. The OR operator selects all rows in which one or both of the specified conditions are true.

[Ch.3] Which of the following would you do to add, drop, or change the options in the same PROC SQL step? a. Restart the step. b. Use a RESET statement. c. Use a CHANGE statement. d. Use a RESTART statement.

B The RESET statement enables you to add, drop, or change the options in the PROC SQL step without restarting the procedure.

[Ch.3] Before you run this query, which of the following changes can you make individually to prevent SAS from remerging and still produce a report? proc sql; select Order_Type, min(Quantity) label="Min Qty" from orion.order_fact; quit; a. Add an ORDER BY clause to sort the output rows by the values of Order_Type. b. Add a GROUP BY clause to group by Order_Type. c. Specify the PROC SQL NOREMERGE option.

B The SELECT list in this query specifies both a non-summarized column and a summarized column. By default, SAS will remerge unless you either group by Order_Type or remove the non-summarized column. Specifying the NOREMERGE option will prevent SAS from remerging but then PROC SQL will not produce a report.

[Ch.7] In the following CREATE VIEW statement, what does the USING clause specify? libname pharma 's:\pmdc'; proc sql; create view pharma.teams as select Name, Team, ProjCode from pharma.projects using libname pharma 'z:\pmdcnew'; quit; a. the physical location in which the pharma.teams view is created b. the physical location in which the source table is stored c. both of the above

B The USING clause specifies the physical location in which the source table is stored so that, when the view is stored and accessed in a different physical location, the view can access the source table.

[Ch.7] Which statement about the following PROC SQL program is correct? proc sql; insert into school.assessment (Student,Level,PreTest, PostTest) values (0095,06,94,91) values (0108,03,71,77); quit; a. It creates a table named school.assessment and adds two rows of values. b. It adds two rows of values to an existing table named school.assessment. c. In the VALUES clauses, the values must be specified in the order that the columns appear in the school.assessment table.

B This INSERT statement adds data to an existing table; it does not create a new table. This form of the INSERT statement uses VALUES clauses to add two rows of data to the existing table school.assessment. The VALUES clauses must specify values in the order that the columns appear in the column list before the first VALUES clause. However, the order of columns in the column list can be different from the order of columns in the table.

[Ch.6] How does the use of the ALL modifier with the UNION, EXCEPT, and INTERSECT operators generally affect efficiency? a. Using the ALL modifier is generally less efficient because it requires an extra pass through the data. b. Using the ALL modifier is generally more efficient because it avoids an extra pass through the data. c. Using the ALL modifier generally has little effect on efficiency because it does not change the number of passes through the data.

B Using the ALL modifier with these three set operators is generally more efficient. Without ALL, PROC SQL makes an extra pass through the data to eliminate duplicate rows.

[Ch.6] n the following code, which set operator will PROC SQL evaluate first? proc sql; select Code, Item from group1 except select Code, Item from group2 intersect select Code, Item from group3 outer union select * from group4; quit; a. EXCEPT b. INTERSECT c. OUTER UNION d. None; PROC SQL evaluates all set operators simultaneously.

B When a SELECT statement has multiple set operators, PROC SQL evaluates INTERSECT first by default. To change the order in which the set operators are processed, you can add parentheses to the statement.

[Ch.1] For which of the following programs will PROC SQL check the syntax without executing the code? a. proc sql; select * from orion.employee_phones; quit; b. proc sql noexec; select * from orion.employee_phones; quit; c. options noexec; proc sql; select * from orion.employee_phones; quit;

B When you specify the NOEXEC option in the PROC SQL statement, PROC SQL checks the syntax without executing the code.

[Ch.3] Which of the following queries can successfully create a report? Note: Assume that all referenced columns appear in the underlying table. a. select Dependents, avg(Salary)as AvgSalary from orion.employee_payroll group by calculated AvgSalary; b. select Dependents, avg(Salary)as AvgSalary from orion.employee_payroll group by Dependents having AvgSalary >40; c. select Dependents, avg(Salary)as AvgSalary from orion.employee_payroll where AvgSalary >40 group by Dependents;

B You can specify columns created by summary functions in the HAVING clause, with or without the CALCULATED keyword. However, you cannot specify columns created by summary functions in the GROUP BY clause. In the WHERE clause, you cannot specify summary functions that have just a single argument.

[Ch.3] Which of the following options will NOT create a label that is displayed as the heading for a column in the output? a. 'Employee Number' b. Employee Number c. Label='Employee ID' d. 'Employee Reference Number'

B You can use the LABEL= column modifier, or the ANSI column modifier, or both within the same SELECT clause.

[Ch.6] By default, the EXCEPT set operator selects all of the rows from the first result set that are not in the second result set. a. yes b. no

B By default, the EXCEPT operator eliminates duplicate rows first. It selects only unique rows from the first result set that are not in the second result set. To select all of the rows from the first result set, you must add the ALL modifier.

[Ch.3] Are options that are included in the PROC SQL statement permanent? a. yes b. no

B Options remain in effect until SAS encounters the beginning of another PROC SQL step or until you change the option.

[Ch.7] What does the following code do? proc sql; create view orion.birth_months as select Employee_ID, Birth_Date, month(Birth_Date) from orion.employee_payroll; quit; a. Creates an empty table in the orion library named birth_months that copies the column definitions from three columns in the orion.employee_payroll table. b. Saves a query in a file named birth_months (a PROC SQL view) that is stored in the orion library. c. Creates a report that shows three columns from the orion.employee_payroll table, and saves the report in a file name orion.birth_months.

B The CREATE VIEW statement creates a file that contains query code. It does not create a table or a report.

[Ch.7] What is the result of submitting the following code? proc sql; create table airline.books (Name char length=12, Code num, Amount num format=dollar12.2); quit; a. a report listing the columns Name, Code, and Amount b. an empty table named airline.books that contains three columns and no rows of data c. a PROC SQL view named airline.books that retrieves three columns fo data from a table with the same name d. an error message in the SAS log indicating the following syntax error: a missing FROM clause

B This CREATE TABLE statement creates an empty table named airline.books that contains three columns and no rows of data.

[Ch.4] Performing an Inner Join

Builds on the syntax from a Cartesian Product One or more join conditions in the WHERE clause specify what rows of the CP to include Can join up to 256 tables using from clause Example: WHERE customs.id = transactions.id Join Keys = columns specified for joining conditions (can have different names in each table and don't have to be sorted) PROC SQL will not order rows in results without ORDER BY clause Must use qualified column names when columns from different tables with same column names; tablename.columnname

[Ch.3] Which of the following options defines the spacing between lines for an output destination that has a physical page limitation? a. OUTOBS b. PRINT c. DOUBLE d. NONUMBER e. NOFLOW f. RESET

C

[Ch.5] What is a subquery? Select one: a. a SUB statement that resides inside a PROC SQL statement or another subquery b. SUB statement that resides inside the clauses of another SELECT statement or another subquery c. a SELECT statement that resides inside the clauses of another SELECT statement or another subquery d. a SELECT statement that resides inside a PROC SQL statement or another subquery

C

[Ch.3] In which of the following queries can you replace the SAS summary function MEAN with the ANSI summary function AVG and get the same results? Select one: a. select Employee_ID, mean(Qtr1,Qtr2,Qtr3,Qtr4) as Mean_Annual from orion.employee_donations; b. select mean(Qtr1,Qtr2,Qtr3,Qtr4) as Mean_Annual from orion.employee_donations; c. select Qtr1, mean(Qtr1) as Mean_Qtr1 from orion.employee_donations; d. select mean(of Qtr1: ) as Mean_Qtr1 from orion.employee_donations;

C ANSI standards only permit one argument in functions

[Ch.2] Which WHERE clause selects rows in which the value of State (a character column) is something other than a missing value? a. where State is not missing b. where State is not null c. where State <> ' '; d. where State not = ' '; e. where State ne ' '; f. all of the above

F All of these WHERE clauses select rows in which the value of State is not a missing value.

[Ch.4] Given these two select statements, what is the difference in the results sets? select * from customers left join transactions on customers.ID=transactions.ID; select * from transactions right join customers on customers.ID=transactions.ID; a. The sets will contain the same matching row, but different data otherwise. b. The sets will contain no matching data. c. The sets will contain the same data in different column order.

C Both of these results sets contain the same data, but the column order is reversed.

[Ch.3] Which value would be displayed first in the output based on this code, if the values returned by the YEAR function are . (missing), 2010, 2011, and 2012? proc sql; select Employee_ID, Employee_Term_Date format=date9., year(Employee_Term_Date) as year from orion.employee_payroll where Employee_Gender='M' and Marital_Status='S' order by year desc; quit; Select one: a. . (missing) b. 2010 c. 2012 d. 2011

C Default order is ascending. If descending keyword specified then missing value would have been first.

[Ch.7] In the table that the following PROC SQL step creates, what type is the BirthDate column? proc sql; create table district.roster (LName char (20), FName char (12), BirthDate date); quit; a. character b. date c. numeric d. temporary

C In SAS data sets, a column's type can be either character or numeric. For ANSI compliance, PROC SQL accepts additional data types in column definitions—for example, date. However, all additional types are converted to either character or numeric in the SAS data set.

[Ch.5] Which of the following is a valid subquery? a. (select Employee_ID, Gender from orion.staff) b. (select Employee_ID from orion.staff;) c. (select Gender from orion.staff) d. (subselect Gender from orion.staff)

C In option a, the SELECT clause specifies two columns, but a subquery must return only a single column. Option b is incorrect because a subquery cannot contain a semicolon. Option d is incorrect because SUBSELECT is not a valid keyword.

[Ch.4] The product_dim table contains 481 rows, one row for each product that Orion Star sells. All Orion Star products are assigned to one of 50 product groups. Which SELECT statement creates a report that shows the number of products in each product group? Select one: a. select Product_Group, count(*) as Products from orion.product_dim; b. select Product_Group, count(*) as Products from orion.product_dim group by Product_Group, Products; c. select Product_Group, count(*) as Products from orion.product_dim group by Product_Group;

C Notice the select clause has both nonsummarized (product_group) and summarized(Products) columns. In order to prevent remerging you must group by the nonsummarized column

[Ch.3] Which ORDER BY clause orders a report by descending State and descending City? a. order by desc State, City b. order by State, City desc c. order by State desc, City desc d. order by desc State, desc City

C The DESC keyword sorts the output rows by both State and City in descending order.

[Ch.7] What happens when you run the following PROC SQL step? proc sql; create table work.birth_months as select Employee_ID, Birth_Date, month(Birth_Date) from orion.employee_payroll; quit; a. PROC SQL creates the birth_months table with the two columns Employee_ID and Birth_Date. The calculated column in the SELECT clause is ignored because it has no alias. b. PROC SQL creates creates the birth_months table with three columns: Employee_ID, Birth_Date, and a column with no name. c. PROC SQL creates creates the birth_months table with three columns: Employee_ID, Birth_Date, and a column with a SAS assigned, unique name such as _TEMA001. d. An error is generated in the log because the ANSI standards for SQL require you to specify an alias for a calculated column in the CREATE TABLE statement.

C The code does not specify an alias for the calculated column, so SAS assigns a unique name to the column.

[Ch.7] What does the following statement do? describe view orion.salesreps; a. creates a report showing all the columns and rows that are currently stored in the table or tables underlying the view orion.salesreps b. in the log, displays the CREATE VIEW statement that was used to create the view orion.salesreps c. in the log, displays a query that defines the view orion.salesreps

C This DESCRIBE VIEW statement displays a query that defines the view in the log. The displayed query might not be exactly the same as the query in the CREATE VIEW statement that originally created the view.

[Ch.3] The orion.product_dim table contains 481 rows, one row for each product that Orion Star sells. You want to modify the following query to create a report that displays the names of suppliers who provide more than 10 categories of products. Which clauses can you add individually to create this report? proc sql; select Supplier_Name, count(Product_Category) as Product_Cats from orion.product_dim group by Supplier_Name; quit; a. where Product_Cats >10 b. where calculated count(Product_Category) >10 c. having Product_Cats >10 d. where count(Product_Category) >10

C This query must use the HAVING clause to subset data after it is grouped. In the HAVING clause, you can either specify a column alias to reference a column calculated in the SELECT clause, or repeat the expression that performs the calculation. You don't need to include the CALCULATED keyword in the HAVING clause.

[Ch.4] Which syntax is appropriate for a self-join? a. select ID, Name, mgr.ID, mgr.Name from employees as emp, organization as org where emp.ID=org.ID; b. select ID, Name, mgr.ID, mgr.Name from employees and emp or mgr, organization as org where emp.ID=org.ID and emp.ID=mgr.ID; c. select emp.ID, emp.Name, mgr.ID, mgr.Name from employees as emp, organization as org, employees as mgr where emp.ID=org.ID and org.Manager_Name=mgr.ID;

C To write a self-join query, select5 from the same table listed twice with different aliases, set up the comparison, and eliminate cases where a particular value would be equal to itself.

[Ch.3] Which of the following programs does NOT position the TITLE statement correctly? a. title 'Manager Report'; proc sql; select Employee_ID from orion.order_fact; quit; b. proc sql; title 'Manager Report'; select Employee_ID from orion.order_fact; quit; c. proc sql; select Employee_ID from orion.order_fact; title 'Manager Report'; quit;

C You must place TITLE and FOOTNOTE statements before the SELECT clause because in the SQL procedure, the SELECT clause runs immediately. Alternatively, you can place the statement before the PROC SQL statement.

[Ch.2] proc sql; create table work.history as select Employee_ID, Start_Date, month(Start_Date) as Start_Month from orion.employee_payroll; quit; a. Employee_ID b. Start_Date c. Start_Month d. SAS will assign a name such as _TEMA001.

C Start_Month is the alias assigned to the calculated column.

[Ch.3] In which of the following queries can you replace the SAS summary function MEAN with the ANSI summary function AVG and get the same results? Select all that apply. a. select mean(Qtr1,Qtr2,Qtr3,Qtr4) as Mean_Annual from orion.employee_donations; b. select Employee_ID, mean(Qtr1,Qtr2,Qtr3,Qtr4) as Mean_Annual from orion.employee_donations; c. select mean(Qtr1) as Mean_Qtr1 from orion.employee_donations; d. select Qtr1, mean(Qtr1) as Mean_Qtr1 from orion.employee_donations;

C, D Unlike most SAS summary functions, ANSI summary functions can take only a single argument.

[Ch.7] Find the syntax error(s) in the following code. Select all that apply. proc sql; create table discounts (Product_ID num format=z12., Start_Date date, End_Date date, Discount num format=percent.) insert into discounts (Product_ID Start_Date End_Date Discount) values (220200200022,'01Mar2013'd, '31Mar2013'd,.35) values (220200200024,'01Mar2013'd, '31Mar2013'd, '.35'); quit; a. CREATE TABLE statement, line 2 b. CREATE TABLE statement, line 3 c. CREATE TABLE statement, line 4 d. INSERT statement, column list e. INSERT statement, second VALUES clause

C, D, E This code has three types of syntax errors: The CREATE TABLE statement has a missing semicolon. In the INSERT statement, the three spaces in the column list need to be replaced with commas. In the second VALUES clause, the numeric value .35 should not be enclosed in quotation marks.

[Ch.6] Using the UNION Operator with CORR Modifier

CORR needed after UNION when column order not the same in the intermediate results sets (especially if column types are different) Removes columns that do not match from final set

[Ch.4] Working with Many to Many Matches

CP treats multiple matches differently than DATA step CP will have all potential combos Data step merges based on first table values of joining variable

[Ch.3] Calculating Multiple Summary Columns

Can create another column sum by adding a second SUM() function

[Ch.3] Calculating Ratios

Can use boolean expressions to create new columns based on conditions of another column Use SUM() to return the totals for each group

[Ch.6] Using a Set Operator in an In Line View

Can use set operation as a nested query in the FROM clause

[Ch.4] Understanding Cartesian Products

Cartesian Product: contains all possible combinations of rows from all tables Created multiple tables listed in FROM clause without a WHERE clause Equal to # of rows in each table multiplied together Result can contain unwanted data Can use CROSS JOIN keyword in FROM clause to create cartesian product (rarely used) Cartesian Product will have matching and unmatching data - will get a note in the log

[Ch. 5] Which WHERE clause is equivalent to the following? where ID in (select employee_ID from orion.employee_adressesses where city = 'San Diego') Select one: a. where ID = (select employee_ID from orion.employee_addresses where city='San Diego') b. where ID = all (select employee_ID from orion.employee_addresses where city='San Diego') c. where ID in (select employee_ID from orion.employee_addresses where city=San Diego) d. where ID = any (select employee_ID from orion.employee_addresses where city='San Diego')

D

[Ch.1] Which of the following is a step boundary? a. a QUIT statement b. a PROC step c. a DATA step d. All of the above

D

[Ch.4] How many tables can be combined using an inner join? a. none b. two tables c. up to 16 tables d. up to 256 tables

D

[Ch.5] Which of the following statements identifies the syntax error in this query? proc sql; select Lastname, Firstname from charity.donors where Donation>1000 and Lastname not in select Lastname from charity.current; quit; a. The subquery must reside inside a HAVING clause. b. The subquery must reference the same table as the outer query. c. The subquery requires its own semicolon. d. Parentheses are required around the subquery

D

[Ch.2] In this code, what will be the name of the calculated column in the newly created table? proc sql; create table work.birth_months as select Employee_ID, Birth_Date, month(Birth_Date) from orion.employee_payroll; quit; a. Employee_ID b. Birth_Date c. Birth_Month d. SAS will assign a name such as _TEMA001.

D Because an alias wasn't provided, SAS will assign a unique name to the column.

[Ch.6] Which of the following statements is true about using multiple set operators in a single SELECT statement? a. You cannot use multiple set operators in a single SELECT statement. b. You can use multiple copies of the same set operator but not different set operators. c. PROC SQL always evaluates the set operators from top to bottom. d. By default, PROC SQL evaluates the INTERSECT set operator before the other set operators.

D By default, PROC SQL evaluates the INTERSECT set operator before the UNION, OUTER UNION, and EXCEPT operators. You can use parentheses to force PROC SQL to evaluate set operators in a different order.

[Ch.3] Which of the following options would you use to change the option that numbers the rows in query output? a. OUTOBS b. PRINT c. DOUBLE d. NONUMBER e. NOFLOW f. RESET

D The NONUMBER options controls whether the SELECT statement includes a column that displays row numbers as the first column of query output in the Output window or HTML output.

[Ch.7] Which PROC SQL step uses the correct syntax to create an empty table named company.travel by copying the column structure from another table named company.expenses that contains data? a. proc sql; create table company.travel as select * from company.expenses; quit; b. proc sql; create table company.travel; insert columns from company.expenses; quit; c. proc sql; create table company.travel as company.expenses; quit; d. proc sql; create table company.travel like company.expenses; quit;

D To create an empty table that copies the column structure from an existing table, you use the CREATE TABLE statement with the LIKE keyword.

[Ch.7] Creating a Table by Defining the Columns

Define columns in the CREATE TABLE statement Specify column definitions in parenthesis and separated by columns after the CREATE TABLE statement Must include Column name and data type (character or numeric), Optionally can include format, identify if date Log will display any data type conversions of columns in new table

[Ch.2] Using DESCRIBE TABLE Statement

Displays list in SAS log of column attributes (name, labels, formats, variable type, length) Statement used in PROC SQL DESCRIBE TABLE table1,table2,...

[Ch.6] Using the EXCEPT Operator

EXCEPT combines queries vertically but only includes rows for the first query without matches in the second query Removes duplicate rows from first query results Columns aligned by position Then removes rows from first results set that exist in the second results set

[Ch.3] Combining Summarized and Non-summarized Columns

For non-summarized columns, the report generates a corresponding row for each row that is processed by the query For summarized columns, a single row is generated When combining these summarized data types the report will repeat individual summary value down each row of the unsummarized data (called remerging) Anytime there is a function with one argument that will give one row of output - but if there are columns selected that are not summarized the output will have a row for each row in the table despite the summary function in the select clause

[Ch.7] Specifying Column Name-Value Pairs iwthe the SET Clause

INSERT INTO table name SET column_name = value Pairs separated by commas No semicolon at the end Can use multiple SET clauses, use one SET clause per row when adding multiple rows Don't need to list the same column or in the same order Log will note how many rows are inserted

[Ch.7] Adding data from one table to another

INSERT INTO tablename (optional column list) SELECT... Order in column list doesn't matter

[Ch.6] Using the INTERSECT Operator

INTERSECT produces two intermediate results sets Duplicates removed from both results sets Columns aligned by position Identify rows in the first result set that match rows in second result set Matching rows selected from first results set and column names determined by names in first set

[Ch.6] Combing Tables Vertically

Important to understand how your data is organized especially if data is stored across multiple tables Easiest to vertically combine queries when they have the same column structure

[Ch.4] using COALESCE Function to Overlay Columns

In PROC SQL JOIN must specify column to use to eliminate the duplicate column To overlay columns use COALESCE function Use in SELECT clause to overlay columns rather than eliminate duplicates

[Ch.4] Complex SQL Joins

In order to read from the same table twice, you must list table twice in FROM clause and provide a different table alias to each occurrence for each pass through the table

[Ch.2] Using Operators in the WHERE Clause

Mnemonic comparison operators are SAS enhancements, not ANSI standards Comparison operators Arithmetic operators Logical operators Special operators (contains, is null, is missing, between, like)

[Ch.2] Calculating Columns Using SAS Dates

Most functions supported in PROC SQL Can create age column using "EXPRESSION as ALIAS"

[Ch.2] Using a Expression to Create Columns and Assigning Alias

New values can be text, numbers, constants, expressions Only appear in report, not permanently in table Need to specify calculation AS ___ to give the new column a name/alias Salary *10 as Bonus

[Ch.6] Using OUTER UNION Operator

OUTER UNION produces all rows and all columns from both queries Intermediate results sets= rows are vertically aligned Columns not aligned Need to use CORR modifier to overlay the common columns CORR modifier will not eliminate nonmathcing columns when used with OUTER UNION ALL modifier cannot be used with OUTER UNION

[Ch.3] Controlling PROC SQL Output with Options

Options are applied to PROC SQL statement until a new PROC SQL step is encountered Can use RESET statement to reset options (can add this mid PROC SQL) OUTOBS=n restricts the number of rows written to report NONUMBER | NUMBER determines whether output contains a ROW column (nonumber is default) NODOUBLE | DOUBLE only affects listing destination (no double is default and single spaces report) NOFLOW | FLOW only affects listing destination and flow causes text to wrap rather than go onto next line as default

[Ch.6] Combining Set Operators

Order of set operators matters INTERSECT will be processed first Other operators processed from top to bottom Can add parenthesis to code to change default order - the operators in parenthesis will be procesed first

[Ch.4] Comparing JOIN Methods

PROC SQL - Does NOT require sorting of data before merge - Does not require use of same name columns in join expressions - Does NOT require only equality expression, can use comparisons in join statement DATA Step - DOES require sorting of data in PROC SORT before merge - Requires merge columns to have same name - Equality required in merge expression

[Ch.1] The Structure of a PROC SQL Step

PROC SQL options; other statements quit; No run statement needed because of run-group processing

[Ch.5] Nested Subquery

Processed from inside most subquery and works outward

[Ch.1] What could you add to the end of this program to stop PROC SQL from running? Select all that apply.

QUIT statement DATA step another PROC step

[Ch.5] Understanding In-Line Views

Query nested in the FROM clause of another query Acts as a virtual table Can contain any clause from SELECT statement except ORDER BY Must be enclosed in parenthesis Can assign an alias to an in-line view Use when you need nested subqueries and you're dealing with pulling from multiple tables

[Ch.4] Working with Columns that have the same name

Results set will include all columns unless in the SELECT clause specifies only single column Must use table qualifier to specify column in SELECT clause Can also use NATURAL JOINS to eliminate duplicate columns: EXAMPLE: from one natural join two;

[Ch.4] Working with Outer Joins

Returns matching and nonmatching rows Tables are processed two tables at a time Use LEFT|RIGHT|FULL JOIN keywords int e FROM clause to specify the type of join Use ON clause to specify join condition Only can join two tables at a time

[Ch.4] Inner Join

Returns matching rows in input tables, excludes nonmatching rows

[Ch.2] Methods fro Viewing Contents & Structure of Table

SELECT Statement Use SELECT statement + FEEDBACK option to create complete list column names Use DESCRIBE TABLE option

[Ch.6] Syntax of Set Operation

SELECT... UNION | OUTER UNION | EXCEPT | INTERSECT <ALL><CORR> SELECT; SELECT statement can have multiple set operators Processes from top to bottom Always only produces one results set Semicolon still only used once, at the very end of the last select query

[Ch.6] OUTER UNION vs. DATA STEP

SET statement in the DATA step can produce the same results as an OUTER UNION

[Ch.2] Which CASE expression is the more efficient?

Shortcut form

[Ch.5] ALL Keyword

Specifies that all of the values obtained from the subquery must satisfy a given condition >ALL true if value of the specified column is greater than the highest value of subquery <ALL true if value is less than the lowest value the subquery returns

[Ch.5] ANY Keyword

Specifies that at least one of a set of values obtained from a subquery must satisfy a given condition in order for the expression to be true =ANY >ANY true when value is greater than the smallest value returned by the subquery <ANY expression is true when value of column is less than the highest value of subquery

[Ch.5] Using an In-LIne View and a Subquery

Split query from multiple tables into multiple parts to help test and check queries

[Ch.6] Using the UNION Operator without Modifiers

Step completed for each query to produce result set Columns aligned by position Duplicate rows removed from combined set (when no ALL modifier) Final set consists of remaining rows Must use ORDER BY clause to ensure specific order Can only align column when they have the same tpye even if they ahve the same name

[Ch.5] Understanding Subqueries

Subquery resides in an outer query in a WHERE or HAVING clause Enclose in parenthesis Returns values to be used in outer queries Only can return one column Subquery does not include semicolon Subquery can reference same of different tables from the outer query SQL quries evaluated from inside out Subquery = inner query = subselect

[Ch.5] Checking for Any Rows Returned by a Subquery

Subset data with outer query based on appropriate conditions Use nested query as condition for subsetting outer query

[Ch.7] Providing Access to Data with a PROC SQL View

Syntax: CREATE VIEW view_name AS query Don't need to specify libref in the FROM clause for tables - can use just one level name Don't use ORDER BY clause in the view query itself for efficiency sake Can check view with DESCRIBE VIEW statement

[Ch.5] If the subquery returns multiple values, you must use the IN operator or a comparison operator with the ANY or ALL keywords. Select one: True False

TRUE

[Ch.6] Set OPreators

UNION OUTER UNION (SAS enhancement) EXCEPT INTERSECT Modifiers: ALL and CORR used to combine tables vertically acts on the intermediate result sets not on the input tables

[Ch.6] How Set Operators Combines Rows and Columns by Default

UNION: produces all unique rows from both results sets; duplicate rows removed; rows from both sets; align columns by position OUTER UNION: produces all rows from both result sets; rows from both sets; produces all columns form both sets EXCEPT: produces unique rows from the first result set that are not in the second; duplicate rows removed; rows from only one set; align columns by position INTERACT: produces unique rows from the first results set that are in the second; duplicate rows removed; rows from only one set; align columns by position

[Ch.7] Creating a Portable PROC SQL View

USING clause to make view portable Add it at the end of the SELECT statement Specify libname clauses for that view

[Ch.3] Adding Column Labels and Formats

Use 'column name' after the column name in the SELECT clause to label a column (select Employee_ID 'Emp ID',) ANSI standard Can use label =' ' after column name in SELECT clause to give it a label (select Employee_ID label ='Emp ID',) SAS Enhancement Can use format = ' ' after column name in SELECT clause (select donation format=dollar6.0) SAS Enhancement

[Ch.2] Using Conditional Logic to Create New Columns

Use CASE expression to create values based on specified condition Evaluated for each row of the table Use with WHEN THEN clauses and ELSE expression Need to use END to close the CASE block Use END AS ALIAS to create alias for column Once it hits a true condition it jumps to END Shortcut form more efficient than standard form Shortcut only works with equal operand - have to use standard if using comparison operands No semicolons

[Ch.3] Selecting Groups with the HAVING Clause

Use COUNT() and GROUP BY clause to get counts of groups Cannot use WHERE clause with summary functions Must use HAVING clause to subset grouped rows HAVING clause can refer to column aliases without using CALCULATED keyword (SAS enhancement) ANSI requires you repeat the calculation expression in the HAVING clause

[Ch.2] Creating and Populating a Table from a Query Result

Use CREATE TABLE statement and AS keyword CREATE TABLE table-name AS query-expression; Use before SELECT statement If columns are created without the AS keyword SAS will automatically come up with column names Only new table is created as output, not a report Can view details of new table using DESCRIBE TABLE

[Ch.2] Using FEEDBACK Option to Display Expanded Query

Use FEEDBACK option after PROC SQL to see list of all column names in SAS log Two level name displayed in log (i.e. accounts.start_date) Query in LOG shows query with column names instead of * Must RESET to disable

[Ch.4] Performing a Full Outer Join

Use FULL JOIN keyword The order of the tables affect the order of the columns in the result set - but that's it

[Ch.3] Grouping Data Using the GROUP BY Clause

Use GROUP BY clause after the WHERE clause to specify which columns to group by Separate multiple columns with commas Can specify column name, column alias, expression, or position # (SAS enhancement) Forces summary statistics to be grouped by column in report Cannot specify an expression that is a summary function, must specify calculated or non-summarized columns Must specify columns in GROUP BY clause in the SELECT clause You do not have to specify the CALCULATED keyword when specifying calculated column in the GROUP BY clause Make sure the GROUP BY clause includes all columns you want to consider in summarizing

[Ch.4] Using ALternate Syntax for Inner Join

Use INNER JOIN keyword to separate table names in FROM clause Processes two tables at a time Use ON clause to specify join condition

[Ch.4] Performing a Left Outer Join

Use LEFT JOIN keyword Result set will include columns from all tables and there will be missing values where there are not matches in the right table

[Ch.7] Creating a Table by Copying the Column Structure

Use LIKE clause in the CREATE TABLE statement CREATE TABLE tablename LIKE tablename; Specify existing table you want to copy No rows will be populated Can use DESCRIBE TABLE statement to view columns of new table

[Ch.3] Ordering Rows with the ORDER BY clause

Use ORDER BY clause to guarantee a specific order of output (original table doesn't change) Separate columns in list with commas Can specify a number in ORDER BY clause to specify variables in SELECT statement to order by Treats missing values as the smallest possible values Default is ascending order - must specify descending order with DESCENDING keyword AFTER the column name Can order by column that is NOT in the SELECT clause and sas will write note to log

[Ch.3] Control Remerging

Use REMERGE | NOREMERGE as option in PROC SQL statement to prevent remerging - you'll get an error in the log

[Ch.4] Performing a Right Outer Join

Use RIGHT JOIN keyword Matches will appear at the top of the results All columns from both tables will be in results but will have missing values in the columns from the left table for nonmatches

[Ch.2] Using SELECT Statement to Display All Columns

Use SELECT * FROM Asterisk displays all columns in table in the order in which they are stored Should specify each table in a different SELECT - FROM statement to prevent them from combining Formatted values and labels will be displayed if stored that way in data table

[Ch.2] Specifying Columns in the SELECT Clause

Use SELECT clause to specify columns Can be listed in any order and will be reflected in that order in the output Separate the names of columns with commas A comma should not appear after the last column name listed in the SELECT clause.

[Ch.3] Summarizing Down a Column

Use SUM(arg1) function in the SELECT statement with just ONE column as the argument Can add label to understand printed report better

[Ch.3] Summarizing Across a Row

Use SUM(arg1, arg2, arg3) function in the SELECT statement Can then add alias, label, and format to the column created by the SUM function Multiple arguments sums across row SUM function ignores missing values

[Ch.3] Adding Titles and Footnotes

Use TITLE and FOOTNOTE statements to create Global statements Use null to clear (title; or footnote;) Must place BEFORE the PROC SQL or SELECT statement

[Ch.6] Combining Three Queries with UNION Operator

Use UNION operator between each query

[Ch.1] Checking PROC SQL Syntax Without Executing the Program

Use VALIDATE statement or NOEXEC option Use VALIDATE statement only before the SELECT statement Must use VALIDATE before each SELECT statement if there are multiple No report created - log will indicate if query has correct syntax NOEXEC in PROC SQL prevents the code from executing and check syntax of all statements Use RESET EXEC statement to reset NOEXEC options

[Ch.7] Using PROC SQL View

Use VIEW in the FROM clause of a proc sql statement

[Ch.4] Specifying Subsetting Conditions for an Outer Join

Use WHERE clause to subset results Consider switching right and left tables to control what nonmatches stay

[Ch.2] Subsetting Rows by Using the WHERE Clause

Use WHERE clause to the SELECT statement with an expression of operators and operands to instruct what values tp subset Do not include any formatting details in a constant Character constants are case sensitive Cannot use name of alias created in SELECT statement - must use CALCULATED or full alias expression Can subset on a column that does not appear in the SELECT statement Can only use a single WHERE clause per SELECT statement

[Ch.3] Adding Constant Text

Use a text string in quotes followed by a comma in a SELECT clause to create text constant Positioning in the SELECT clause impacts order of columns in report Don't include quotes for numeric constants (select Product_ID, 'New Discount is:', .15)

[Ch.7] Using a Query in the CREATE TABLE Statement

Use query in the CREATE TABLE statement Often used to create a new table that's a subset of an existing table CREATE TABLE table name AS ... Only one semicolon at the end Report output is suppressed when this is used Can verify contents of new table with DESCRIBE TABLE statement

[Ch.5] Using a Subquery That Returns a Single Value

Use subquery in a HAVING clause to subset by single value condition that is always up to date

[Ch.3] Counting the Number of Rows

Use what you know about the table and functions to count number of rows Use COUNT() function with column name or * Column name will count number of rows in column with nonmissing values * will give total number of rows in the table FREQ() and N() cannot use * and must use column name

[Ch.2] Eliminating Duplicate Rows Using the DISTINCT Keyword

Use with the SELECT clause after SELECT and before column names The DISTINCT keyword comes after the SELECT keyword and before the list of columns. Will apply to all columns listed in the SELECT clause SELECT DISTINCT Job_Title, Department, Can use UNIQUE in place of DISTINCT in PROC SQL (but not ANSI standard)

[Ch.7] Creating PROC SQL Views

View = stored query code but no actual data Virtual table, referenced like a table, has a name Must reside in the same library as source table Cannot have same name as table that exists

[Ch.2] Subsetting with Calculated Values Using CALCULATED keyword

WHERE clauses cannot find aliases created in the SELECT statement Option 1: Repeat calculation in WHERE clause (Salary *10 <3000) Option 2: Use CALCULATED keyword in WHERE or SELECT clause to tell SAS to check select clause for how the alias is calculated

[Ch.1] Along with reports, can a PROC SQL query also create SAS data files, SAS data views, and DBMS tables?

YES

[Ch.1] In your PROC SQL code, can you include SAS features that do not conform to the ANSI standard?

YES

[Ch.1] Does PROC SQL run the clauses of a SELECT statement in a different order than you write them?

Yes

[Ch.4] Methods of Combining Tables Horizontally

You must consider how you want to combine rows - what variable are you matching with? Can have nonmatching rows - must decide how to treat them SQL JOIN is when you use a SELECT statement with more than one table in the FROM clause to merge tables horizontally Outer Join

[Ch.5] Operators That Accept Multiple Values

operator before subquery indicates if the outer query takes multiple values or not IN operator takes multiple values EQUAL operator only accepts one value If the subquery returns multiple values must use IN operator or comparison operator with ANY or ALL keyword