ST522 Midterm

B)false The SYMBOLGEN option displays macro variable values as they are resolved, not before they are resolved.

The SYMBOLGEN option displays macro variable values before they are resolved. A)true B)false

C)WHERE or HAVING When a SELECT statement resides in a WHERE or HAVING clause, it is called a subquery.

A subquery can reside in which of the following clauses? A)SELECT or FROM B)SELECT or WHERE C)WHERE or HAVING D)WHERE or FROM

B) no A subquery must return values only from a single column.

A subquery can return several rows of data, but can a subquery return values from multiple columns? A) yes B) no

A)true

The %MEND statement ends a macro definition. A)true B)false

E)b and c The two types of macro parameters are keyword parameters and positional parameters.

What are the two types of macro parameters? A)user B)keyword C)positional D)a and b E)b and c

D)automatic and user-defined

What are the two types of macro variables? A)SYMPUT and SYMPUTX B)simple and complex C)character and numeric D)automatic and user-defined

D)OPTIONS MCOMPILENOTE=ALL;

Which of the following statements causes the log to display a note when a macro definition is compiled? A)OPTIONS MLOG=YES; B)OPTIONS MNOTE=LOG; C)OPTIONS MCOMPILE=NOTE; D)OPTIONS MCOMPILENOTE=ALL;

B) the SYMBOLGEN system option

Which of the following statements enable you to view the values of the macro variables without using them in a report? A) the SYMBOLLOG system option B) the SYMBOLGEN system option C) the NOFEEDBACK PROC SQL option D) the LABEL= option

C)64K

What is the maximum length of a macro variable? A)16K B)32K C)64K D)96K

A) Yes This inner join can create this result set. An inner join creates a Cartesianproduct first, so it returns all possiblecombinations of the rows with matching join key values.

Acme.Orders lists information for each order, including the employee ID of the salesperson. Some employee IDs might appear multiple times, and others not at all. Acme.Phones lists each employee ID twice, once for each type of phone number. Can the following join create a result set like the one shown below, which lists two rows for each order? select Order_ID, coalesce(o.Emp_ID, p.Emp_ID) as Emp_ID, Phone_Number as Phone from acme.orders as o, acme.phones as p where o.Emp_ID=p.Emp_ID order by Order_ID; Partial Table Orders Emp_ID | Order_ID 121039 | 1230058123 120174 | 1230147441 Partial Table Phones Emp_ID | Type | Phone_Number 120101 | Home | +61(2)5555-1849 120101 | Work | +61(2)5551-0001 Partial Results Set Order_ID | Emp_ID | Phone 1230058123 | 121039 | +1(305)555-7540 1230058123 | 121039 | +1(305)555-7540 A) yes B) no

B) no No, an in-line view can be nested only in the FROM clause in the place of a table name. In addition, an in-line view can return values from one column or multiple columns.

An in-line view is nested in the WHERE clause. A) yes B) no

C) Add an additional column to the SELECT list 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 prevents SAS from remerging, but then PROC SQL does not produce a report.

Before you run this query, which of the following changes can you make 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 or Order_Type B) Add a GROUP BY clause to group by Order_Type C) Add an additional column to the SELECT list D) Specify the PROC SQL NOREMERGE option

A) yes Although they do not remove the duplicate rows at the same point, they do all remove duplicate rows.

By default, do the EXCEPT, INTERSECT, and UNION set operators remove duplicate rows from the query results? A) yes B) no

B) %macro-name

How is a macro called? A) %INVOKE B) %macro-name C) %LET macro-name D) %CALL macro-name

A) 1 The INTERSECT operatorreturns the unique rows that occur in both queries.

How many rows will this query produce? proc sql; select * from a intersect select * from b; quit; Table A ID | Pet 1 | Cat 1 | Dog 1 | Pig Table B ID | Pet 1 | Cat 2 | Cow 3 | Dog A) 1 B) 2 C) 5 D) 6

A) yes Like all PROC SQL options, the FEEDBACK option remains in effect until PROC SQL encounters a step boundary.

In this program, if you add another SELECT statement above the QUIT statement, will the second SELECT statement generate an expanded query in the SAS log? proc sql feedback; select * from acme.payroll where salary<750000; quit; A)yes B)no

D) select mean(Qtr1) as Mean Qtr1 from orion.employee_donations; Unlike most SAS summary functions, ANSI summary functions can take only a single argument. Also, because PROC SQL is ANSI standard, shortcurs to reference a range of variables in an argument list within a function are not acceptable in PROC SQL as they are in other SAS procedures

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? 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(of Qtr1:) as Mean_Qtr1 from orion.employee_donations; D) select mean(Qtr1) as Mean Qtr1 from orion.employee_donations;

B) No Assigning an alias to a column is optional

Is an alias required for a column created in the SELECT clause? A) Yes B) No

A)true

Macro parameters are stored in a local symbol table. A)true B)false

A)true

Macro variables are stored in the global symbol table. A)true B)false

A) True

PROC SQL with set operators handles columns and rows depending on the specific set operator and keywords used in the set operation. A) True B) False

B) no If your data changes frequently and a PROC SQL view is referenced multiple times within the same program, then the view might return different data to different steps within the same program.

Suppose you have a large SAS program that accesses transactional data that changes several times throughout the different steps. The data for the different steps needs to be the same for each step. Should you use a view to obtain the data? A) yes B) no

B)false The %MACRO statement begins a macro definition.

The %START statement begins a macro definition. A)true B)false

B)false The %SYSFUNC macro language function executes SAS functions.

The %SYSEXEC macro language function executes SAS functions. A)true B)false

B) False - the addition operator returns a missing value if any operand is missing

The following SELECT statements return the same results: - select sum(Qtr1,Qtr2,Qtr3,Qtr4) from orion.employee_donations; - select Qtr1+Qtr2+Qtr3+Qtr4 from orion.employee_donations; A) True B) False

A) yes

To guarantee the order of rows in the output, must you use an ORDER BY clause? A) yes B) no

A) The subquery succeeds and returns a single value. When the expression includes a comparison operator, such as > or =, the subquery must return only a single column value.

What results do you think you would get from the following query? proc sql; title "Job Titles"; title2 "That Earn More Than"; title3 "All Level IV Employees"; select Job_Title, Salary from orion.staff where Salary > (select max(Salary) from orion.staff where Job_Title  contains 'IV'); quit; A) The subquery succeeds and returns a single value. B) The query fails and an errormessage is printed in the log. C) The subquery succeeds and returns multiple values. D) The query fails and a warning message is printed in the log.

C) order by State desc, City desc

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

D)where 75000<=calculated NewSalary<=100000 The SAS keyword CALCULATED enables you to use the results of an expression in the same SELECT clause or in the WHERE clause.

Which WHERE clause would subset rows based on NewSalary, an alias created in the SELECT clause? A)where 75000<=Salary calculated <=100000 B)where NewSalary>=75000 and NewSalary<=100000 C)where 75000<= calculated Salary<=100000 D)where 75000<=calculated NewSalary<=100000

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; To write a self-join, select 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.

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 emplID =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;

A)true

With a mixed parameter list, positional parameters must be listed first. A)true B)false

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

A self-join requires reading data from different rows of the same table. A) True B) False

B) no The SQL join does not require tables to be indexed or sorted. In theory, a Cartesian product is produced. From the result of the Cartesian product, the join conditions are evaluated and the rows can be joined on equal join conditions or on unequal (inequality) conditions.

An SQL join requires the tables to be indexed or sorted and can be performed only on an equality. A) yes B) no

D) 6 The OUTER UNION set operator selects all rows from both query results.

How many rows will this query produce? proc sql; select * from a outer union select * from b; quit; Table A ID | Pet 1 | Cat 1 | Dog 1 | Pig Table B ID | Pet 1 | Cat 2 | Cow 3 | Dog A) 1 B) 2 C) 5 D) 6

D) 5 The UNION set operator returns unique rows fromboth queries.

How many rows will this query produce? proc sql; select * from a union select * from b; quit; Table A ID | Pet 1 | Cat 1 | Dog 1 | Pig Table B ID | Pet 1 | Cat 2 | Cow 3 | Dog A) 2 B) 3 C) 4 D) 5

B) 2 The EXCEPT set operator selects unique rows from the first query that are not found in the second query.

How many rows will this query produce? proc sql; select * from a except select * from b; quit; Table A ID | Pet 1 | Cat 1 | Dog 1 | Pig Table B ID | Pet 1 | Cat 2 | Cow 3 | Dog A) 1 B) 2 C) 5 D) 6

C) 6 Because the columns in both tables correspond, no columns are removed. The ALL keyword instructs PROC SQL to retain all duplicate rows in the results.

How many rows will this query produce? proc sql; select * from a union all select * from b; quit; Table A ID | Pet 1 | Cat 1 | Dog 1 | Pig Table B ID | Pet 1 | Cat 2 | Cow 3 | Dog A) 2 B) 4 C) 6 D) 8

C)more than one error This complex join has more than one error. In the outer query's FROM clause, a parenthesis should precede the in-line view. An in-line view should end with a parenthesis and not a semicolon. Also, in-line views cannot contain an ORDER BY clause.

How many syntax errors does the following complex join have? proc sql; select count(Supplier_Name) 'Low Volume Suppliers' from select Supplier_Name, count(Quantity) as Num_Sold from orion.product_dim as p, orion.order_fact as o where p.Product_ID= o.Product_ID group by Supplier_Name order by Num_Sold; where Num_Sold < 5; quit; A)no errors B)one error C)more than one error

C) more than one error 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.

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) no errors B) one error C) more than one error

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

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) no COUNT(*) counts the total number of rows in a table or in a subset. However, when you specify a column as an argument for COUNT, the function counts the number of rows in the tables or in a subset that have a nonmissing value for that column

In this PROC SQL program, given the partial data, do the two SELECT statemetns generate the same results? Employee_ID | Qtr1 | Qtr2 | Qtr3 | Qtr4 120265 | . | . | . | 25 120267 | 15 | 15 | 15 | 15 120269 | 20 | 20 | 20 | 20 120270 | 20 | 10 | 5 | . 120271 | 20 | 20 | 20 | 20 proc sql; select count(*) from orion.employee_donations; select count(QTR1) from orion.employee_donations; quit; A)yes B)no

D) SAS assigns a name such as _TEMA001 Because an alias was not provided, SAS assigns a unique name to the column.

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 assigns a name such as _TEMA001

A) yes A view builds the data when the view is executed and prevents the user from accessing the source tables. The view will retrieve the most current data.

Suppose you need to use the most up-to-date data from multiple tables for a user that should not be able to alter the source tables. Should you use a view? A) yes B) no

A)yes Using DISTINCT to select unique values of one or more columns conforms to the ANSI standard.

The DISTINCT keyword is used to select unique values of one or more columns. A)yes B)no

A) select coalesce(w.ID,l.ID) as ID, Type, Bin from widgets as w left join locations as l on w.ID=l.ID order by w.ID;

The Widgets table stores information about the most popular items sold in a hardware store. The Locations table stores information about the location of some of the items in the store. Which query produces the result set that is shown below? Table Widgets ID | Type 89 | FL 46 | UI 47 | TA 92 | QY Table Locations ID | Bin 1 | B98 46 | B63 74 | B02 85 | B98 99 | B02 Results Set ID | Type | Bin 46 | UI | B63 47 | TA | 89 | FL | 92 | QY | A) select coalesce(w.ID,l.ID) as ID, Type, Bin from widgets as w left join locations as l on w.ID=l.ID order by w.ID; B) select coalesce(w.ID,l.ID) as ID, Type, Bin from widgets as w right join locations as l on w.ID=l.ID order by w.ID;

B) select Product_Group, count(*) as Products from orion.product_dim group by Product_Group; To aggregate summary statistics by the values of the non-summarized column, Product_Group , the queery must use a GROUP BY clause that specifies the Product_Group column. This report is grouped by only one column. Also, the GROUP BY clause cannot specify a summarized column, like Products.

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? 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; C) select Product_Group, count(*) as Products from orion.product_dim group by Product_Group, Products;

D)Outputs the column name for a subset of data that includes only the TABLES table from dictionary.tables. The FROM clause indicates the dictionary table used as the source of the data. The WHERE clause indicates that the data is subset to only output the data for the memname or table called TABLES.

What does this query do? proc sql; select name from dictionary.tables where memname='TABLES'; quit; A)Outputs the table name for a subset of data that includes only the TABLES column from dictionary.tables. B)Outputs the column TABLES for a subset of data that includes only the name table from dictionary.tables. C)Outputs the column name for a subset of data that includes only the dictionary.tables table from the TABLES table. D)Outputs the column name for a subset of data that includes only the TABLES table from dictionary.tables.

B) PROC SQL checks the syntax and displays information in the log but does not update the table. PROC SQL checks the syntax and displays information in the SAS log but does not execute the statements.

What happens if you specify the NOEXEC option and then submit this PROC SQL statement? update work.sales set Salary=Salary * 1.03 where Job_Title='Sales Rep. I'; A) Nothing happens. PROC SQL does not process the step at all. B) PROC SQL checks the syntax and displays information in the log but does not update the table. C) PROC SQL checks the syntax and displays information in the log. If there are no syntax errors, PROC SQL then updates the table. D) PROC SQL skips the syntax check and updates the table if it can.

A)a SELECT statement that resides inside the clauses of another SELECT statement or another subquery A subquery resides inside the clauses of another SELECT statement or subquery.

What is a subquery? A)a SELECT statement that resides inside the clauses of another SELECT 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 a PROC SQL statement or another subquery D)a SUB statement that resides inside a PROC SQL statement or another subquery

A) %LET

What macro language statement creates macro variables? A) %LET B) %PUT C) %MACRO D) %CREATE

B) an empty table named airline.books that contains no rows of data and three columns The CREATE TABLE statement creates a table that contains no rows and three columns. In order to add row values to this empty table, you would need to use a technique such as the PROC SQL with the INSERT INTO statement.

What will be the result of executing this 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 no rows of data and three columns C) a table named airline.books that compiles the view definition and stores the SELECT statement D) an empty view named airline.books that contains no rows of data

B) select distinct Customer_Type_ID, Country  from orion.customer; To select unique combinations of the values of multiple columns, you place the DISTINCT keyword once in the SELECT clause before the entire column list.

Which SELECT statement creates a report that displays only the unique combinations of values of Customer_Type_ID and Country? A) select distinct Customer_Type_ID Country  from orion.customer; B) select distinct Customer_Type_ID, Country  from orion.customer; C) select distinct Customer_Type_ID, distinct Country from orion.customer; D) select Customer_Type_ID, Country  from orion.customer  where distinct Customer_Type_ID and Country;

C) where ID = any (select employee_ID from orion.employee_addresses where city='San Diego') The original query uses the IN operator in the WHERE clause because it returns multiple values. The use of the =ANY keyword returns equivalent results.

Which WHERE clause is equivalent to the following? where ID in (select employee_ID from orion.employee_addresses where city='San Diego') 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 = any (select employee_ID from orion.employee_addresses where city='San Diego') D) where ID in (select employee_ID from orion.employee_addresses where city=San Diego)

A)where customer.Customer_ID= order_fact.Customer_ID 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.

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) SELECT The SELECT clause is written incorrectly. The columns that appear in the SELECT clause list must be separated by commas, not spaces.

Which clause in the following PROC SQL program is written incorrectly? proc sql; select Style Area Rooms from Realty.Houses where Area > 1000; quit; A)SELECT B)FROM C)WHERE

A) Salary *.01 as Increase 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, not the expression.

Which expression is valid in the SELECT clause? A) Salary *.01 as Increase B) Bonus=Salary*.03 C) Job_Level as Scan (Job_Title,1,' ') D) Salary*.03 Bonus

C) embedding the LIBNAME statement with a USING clause when creating the view Use the embedded LIBNAME statement in the USING clause to make the view definition portable. If you use a one-level name for the source table in a view definition, the view and the source table must reside in the same location.

Which method would enable a view to be portable, that is, enabling it to function correctly if the underlying tables in the view are stored in different locations than the view? A) using a one-level name for the source table in the FROM clause in the view definition and storing the view in a different location as the source table B) using a two-level name for the source table in the FROM clause in the view definition and storing the view in the different location as the source table C) embedding the LIBNAME statement with a USING clause when creating the view

B)case when 20<=Age<=29 then '20-29' When you need to use a comparison operator other than equal, put the case operator in the WHEN-THEN clause.

Which of the following CASE expressions is correct? A)case Age when 20<=Age<=29 then '20-29' B)case when 20<=Age<=29 then '20-29'

E) all of the above All of this information can be stored as a text string in a macro variable.

Which of the following can be stored in a macro variable? A) 9.2 B) 10:32 C) 1352050 D) Soccerballs E) all of the above

B) &macro-variable-name

Which of the following correctly references a macro variable? A) @macro-variable-name B) &macro-variable-name C) %macro-variable-name D) #macro-variable-name

B) %PUT _user_;

Which of the following enables you to view user-defined macro variables? A) &PUT _user_; B) %PUT _user_; C) &LET put_user; D) %LET put_user;

B) contains actual values and retrieves values upon execution A view does not contain actual values. It contains instructions on how to retrieve the values upon execution of the compiled view instructions.

Which of the following is not a characteristic of a PROC SQL view? A) is a query expression that is given a name and stored for later use B) contains actual values and retrieves values upon execution C) can be created from a simple or complex query expression D) can be derived from one or more tables or views

B) the operator in the HAVING clause in the outer query The operator in HAVING clause in the outer query specifies how many values it expects from the inner query.

Which of the following items controls whether the outer query expects a list of values or a single value from the subquery? A) the operator in the WHERE clause in the inner query B) the operator in the HAVING clause in the outer query C) the operator closest to the PROC SQL keywords D) the operator in the WHERE statement in the inner query E) the operator in the HAVING statement in the outer query

A) title "The Sales of the &Country Offices"; When you reference a macro variable, you must precede the macro variable name with an ampersand (&). You can reference macro variables anywhere in SAS code. When you reference a macro variable within a text string, you must enclose the text string within double quotation marks. The macro processor will not resolve macro variable references that appear within single quotation marks.

Which of the following lines of code correctly reference a macro variable? A) title "The Sales of the &Country Offices"; B) where salary > 'avgSal'; C) title 'The Sales of the &Country Offices';

A) 'Employee Number' the LABEL= column modifier is not ANSI standard. It is a SAS enhancement to specify labels. You can use the ANSI column modifier, the LABEL= column modifier, or both within the same SELECT clause.

Which of the following options uses an ANSI standard column modifier to 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) Header=Employee Reference Number

D)NONUMBER - the NONUMBER/NUMBER options control 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

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

B) proc sql; title 'Manager Report'; select Employee_ID from orion.order_fact; quit; 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

Which of the following programs position the TITLE statement correctly? A) proc sql; select Employee_ID from orion.order_fact; quit; title 'Manager Report'; 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;

A) select phones.Employee_ID, Employee_Name, Phone_Number from company.phones, company.address; The query that generates a Cartesian product includes both matching rows and non-matching rows in the result set. The inner join produces a result set that contains only matching rows.

Which of the following queries generates result sets that include both matching and non-matching rows? A) select phones.Employee_ID, Employee_Name, Phone_Number from company.phones, company.address; B) select phones.Employee_ID, Employee_Name, Phone_Number  from company.phones, company.address  where phones.Employee_ID = address.Employee_ID; C) select phones.Employee_ID, Employee_Name, Phone_Number  from company.phones inner join company.address  on phones.Employee_ID = address.Employee_ID;

B)OPTIONS MPRINT;

Which of the following statements causes the log to display SAS code generated as a result of macro execution? A)OPTIONS MLOG; B)OPTIONS MPRINT; C)OPTIONS LOG=MACRO; D)OPTIONS LOGCOMPILE;

B)Parentheses are required around the subquery. A subquery has to be enclosed in parentheses.

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 requires its own semicolon. B)Parentheses are required around the subquery. C)The subquery must reside inside a HAVING clause. D)The subquery must reference the same table as the outer query.

A)You can use the information only from dictionary tables and views using traditional SAS programming. At initialization, SAS creates special Read-Only dictionary tables and views that contain data or metadata about each SAS session or batch job. You can access the tables using PROC SQL or you can use traditional SAS programming to access the view.

Which of the following statements is not accurate regarding dictionary tables and views? A)You can use the information only from dictionary tables and views using traditional SAS programming. B)They are created when SAS initializes the session. C)They are Read-Only tables and views. D)They contain data or metadata about the SAS session

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

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) proc sql; describe table dictionary.columns; quit;

Which of the programs writes the column details of dictionary.columns to the SAS log? A) proc sql; describe table dictionary.dictionaries; quit; B) proc sql; describe table dictionary.columns; quit; C) proc sql; describe view dictionary.columns; quit;

A) proc sql noprint; select avg(Salary) into :MeanSalary from store.employee; quit; The INTO clause creates one macro variable and assigns the value from the first column of the first row of the query result.

Which of these queries would place one value from the first row returned by the SQL query into a macro variable? A) proc sql noprint; select avg(Salary) into :MeanSalary from store.employee; quit; B) proc sql noprint; select avg(Salary), Date into :MeanSalary, :DueDate from store.employee; quit; C) proc sql noprint; select No, Street, City, State, Zip, Country into :Address1-:Address6 from store.employee; quit;

D)proc sql feedback; select * from acme.staff; quit; To display an expanded query, which contains a list of qualified column names, in the SAS log, you add the FEEDBACK option to the PROC SQL statement above the SELECT statement.

Which program correctly displays a list of qualified column names in the SAS log? A)proc sql;describe table acme.staff; quit; B)proc sql;describe acme.staff;  quit; C)proc sql noexec;select * from acme.staff; quit; D)proc sql feedback; select * from acme.staff; quit;

B) select Dependents,avg(Salary) as AvgSalary from orion.employee-payroll group by Dependents having AvgSalary>40; 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 function that have only a single argument

Which query can successfully create a report? 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, month(Employee_Hire_Date) as HireMonth, avg(Salary) as AvgSalary from orion.employee_payroll where calculated AvgSalary group by Dependents, HireMonth D) select Dependents,avg(Salary) as AvgSalary from orion.employee_payroll where AvgSalary>40 group by Dependents;

C) select coalesce(g1.Tag,g2.Tag) as Tag from group1 as g1 full join group2 as g2 on g1.Tag=g2.Tag; The full join with the COALESCE function creates this result set. To overlay two columns, you must specify the two columns as arguments in the COALESCE function.

Which query joins the tables Group1 and Group2 and overlays the Tag columns to produce the result set shown below? Table Group1 Tag | FName a | Fred d | Sek e | Lars Table Group2 Tag | LName b | Zhou c | Amos Result Set Tag a b c d e A) select g1.Tag from group1 as g1 group2 as g2 where g1.Tag=g2.Tag; B) select coalesce(Tag) as Tag from group1 as g1 full join gro p2 as g2 on g1.Tag=g2.Tag; C) select coalesce(g1.Tag,g2.Tag) as Tag from group1 as g1 full join group2 as g2 on g1.Tag=g2.Tag;

C) Result Set 3 Prod_ID | Color | Price 1 | Blue | 12.99 The SELECT clause qualifies the Prod_ID column name, so the result set has only one Prod_ID column. This is an inner join, so the first WHERE expression selects only the rows that have matching values of Prod_ID. The second WHERE expression further subsets the rows so that the result table contains only the one row in which the value of Color is Blue.

Which result set does the following join create? Table Products Prod_ID | Color 1 | Blue 2 | Red 3 | Red 4 | Yellow 5 | Blue 6 | Yellow Table SaleItems Prod_ID | Price 1 | 12.99 4 | 45.99 select products.Prod_ID, Color, Price from products, saleitems where products.Prod_ID= saleitems.Prod_ID and Color='Blue'; A) Result Set 1 Prod_ID | Color | Prod_ID | Price 1 | Blue | 1 | 12.99 5 | Blue | 5 | . B) Result Set 2 Prod_ID | Color | Price 1 | Blue | 12.99 5 | Blue | . C) Result Set 3 Prod_ID | Color | Price 1 | Blue | 12.99

C) creates a stored query expression that does not include the data The CREATE TABLE statement does not create a stored query expression that does not include data.

Which statement is not correct regarding the CREATE TABLE statement? A) creates a new table from an existing table or set of tables B) populates the table with data from the result of the query C) creates a stored query expression that does not include the data D) creates a table from a simple query expression E) creates a table from a complex query expression

B) The INOBS= option restricts the number of rows that PROC SQL reads from the table (or tables). The INOBS= option is a PROC SQL option, not a system option, so you specify it in the PROC SQL statement. The INOBS= option restricts the number of rows that PROC SQL reads from all tables specified in the FROM clause. If a PROC SQL step produces output without the INOBS= option, it does not necessarily produce output when you add the INOBS= option.

Which statement is true about the INOBS= option? A) You specify the INOBS= option in the OPTIONS statement. B) The INOBS= option restricts the number of rows that PROC SQL reads from the table (or tables). C) The INOBS= option restricts the number of rows that PROC SQL writes to output. D) If PROC SQL step produces output without the INOBS= option, it also produces output when you add the INOBS= option.

B) Generally, using the keyword ALL is more efficient because it avoids an extra pass through the data. Not using the keyword ALL is generally less efficient because it requires PROC SQL to make an extra pass through the data to eliminate duplicate rows.

Which statement is true about using the keyword ALL in a set operation? A) Generally, using the keyword ALL is less efficient because it requires an extra pass through the data. B) Generally, using the keyword ALL is more efficient because it avoids an extra pass through the data. C) PROC SQL does not allow duplicate rows to remain eligible for processing. D) The keyword ALL is not an implied part of the OUTER UNION set operator and is not necessary.

A) When used with the EXCEPT operator, the CORR keyword instructs PROC SQL to position by same-name columns and eliminate columns that do not have the same name in both tables from the final results. When the CORRESPONDING keyword is used with EXCEPT and INTERSECT, it instructs PROC SQL to align columns that the query results have in common. The OUTER UNION CORR aligns by like column names in both tables and includes columns that do not have the same name in both tables.

Which statement is true about using the keyword CORR in a set operation? A) When used with the EXCEPT operator, the CORR keyword instructs PROC SQL to position by same-name columns and eliminate columns that do not have the same name in both tables from the final results. B) When used with the INTERSECT operator, the CORR keyword instructs PROC SQL to overlay columns by position in the final results. C) When used with the OUTER UNION operator, the CORR keyword instructs PROC SQL to eliminate columns that do not have the same name in both tables from the final results.

B)The subquery can run as a stand-alone query. A noncorrelated subquery can be extracted and run independently of the outer query.

Which statement is true regarding a noncorrelated subquery? A)The subquery is known as a virtual table. B)The subquery can run as a stand-alone query. C)The outer query passes a value to the inner query before it can execute. D)The subquery must be enclosed in brackets.

C) describe view orion.salesreps; The DESCRIBE VIEW statement writes the stored query expression of a view to the log.

Which statement would you use to investigate the contents of a view named Salesreps stored in the orion library? A) dictate orion.salesreps; B) describe table orion.salesreps; C) describe view orion.salesreps; D) print view orion.salesreps;

D) 2012 - descending order specifies to display values from the largest to the smallest numeric or character value. PROC SQL treats null or missing values as the smallest possible value.

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 Empliyee_Gender='M' and Marital_Status='S' order by year desc; quit; A) . (missing) B) 2010 C) 2011 D) 2012

B) no No. Because the tables had no duplicate rows and the columns are named the same, the keywords cause no changes.

Will adding the CORR and ALL keywords to the EXCEPT operator change the number of rows that the query produces? proc sql; select * from a except select * from b; quit; Table A ID | Pet 1 | Cat 1 | Dog 1 | Pig Table B ID | Pet 1 | Cat 2 | Cow 3 | Dog A) yes B) no

B) no The CREATE VIEW statement stores the SELECT statement. It does not create a report. PROC SQL builds the data each time the view is referenced rather than when the view is created.

Will this code generate a report? proc sql; create view work.birth_months as select Employee_ID, Birth_Date, month(Birth_Date) from orion.employee_payroll; quit; A) yes B) no