Question 25: Which PROC SQL query will remove duplicate values of MemberType from the query output, so that only the unique values are listed?

490 SAS Base Certificarion Practice Questions for A00-211  | 356+ SAS Advanced Certification Practice Questions for A00-212

SAS Combo offer

Question 25: Which PROC SQL query will remove duplicate values of MemberType from the query output, so that only the unique values are listed?

1. proc sql nodup;
select membertype
from sasuser.frequentflyers;
2. proc sql;
select distinct(membertype)
as MemberType
from sasuser.frequentflyers;
3. proc sql;
select unique membertype
from sasuser.frequentflyers
group by membertype;
4. proc sql;
select distinct membertype
from sasuser.frequentflyers;

Correct Answer : 4

To remove duplicate values from PROC SQL output, you specify the DISTINCT keyword before the column name in the SELECT clause.

490 SAS Base Certificarion Practice Questions for A00-211  | 356+ SAS Advanced Certification Practice Questions for A00-212

SAS Combo offer

Question 24: Which clause in the following program is incorrect? proc sql; select sex,mean(weight) as avgweight

490 SAS Base Certificarion Practice Questions for A00-211  | 356+ SAS Advanced Certification Practice Questions for A00-212

SAS Combo offer

Question 24: Which clause in the following program is incorrect?
proc sql;
select sex,mean(weight) as avgweight
from company.employees company.health
where employees.id=health.id
group by sex;

1.  SELECT

2.  FROM

3.  WHERE

4.  GROUP BY

Correct Answers: 2
Exp : The table names that are specified in the FROM clause must be separated by commas. Note that you can specify columns in the WHERE clause that are not specified in the SELECT clause.

490 SAS Base Certificarion Practice Questions for A00-211  | 356+ SAS Advanced Certification Practice Questions for A00-212

SAS Combo offer

Question 23: Consider the following SAS log: 229 data sasuser.ranch sasuser

490 SAS Base Certificarion Practice Questions for A00-211  | 356+ SAS Advanced Certification Practice Questions for A00-212

SAS Combo offer

Question 23: Consider the following SAS log:
229 data sasuser.ranch sasuser.condo / view = sasuser.ranch;
230 set sasuser.houses;
231 if style = ‘RANCH then output sasuser.ranch;
232 else if style = ‘CONDO’ then output sasuser.condo;
233 run;
NOTE: DATA STEP view saved on file SASUSER.RANCH.
NOTE: A stored DATA STEP view cannot run under a different operating system.
234
235 proc print data = sasuser.condo;
ERROR: File SASUSER.CONDO.DATA does not exist.
236 run;
NOTE: The SAS System stopped processing this step because of errors.

Which one of the following explains why the PRINT procedure fails ?

1. nbsp; nbsp; SASUSER.CONDO is a stored DATA step program.

2. nbsp; nbsp; A SAS data file and SAS data view cannot be created in the same DATA step.

3. nbsp; nbsp; A second VIEW=SASUSER.CONDO option was omitted on the DATA statement.

4. nbsp; nbsp; The view SASUSER.RANCH must be processed before SASUSER.CONDO is created.

Correct Answer : 4

A data step view contains a partially compiled data step program that can read data from a variety of source…
The compiled code doesn’t take up too much room for storage
the view= option tells sas to compile, but not to execute, the source program and to store the compiled code in the input data step that is named in the option
Note:if you specify additional data files in the data statement, sas creates these data files when the view is processed in a subsequent data or proc step. Therefore, you need to reference the data view before you attempt to reference the data file in later steps

490 SAS Base Certificarion Practice Questions for A00-211  | 356+ SAS Advanced Certification Practice Questions for A00-212

SAS Combo offer

Question 22: Which statement is true regarding the use of the PROC SQL step to query data that is stored in two or more tables?

490 SAS Base Certificarion Practice Questions for A00-211  | 356+ SAS Advanced Certification Practice Questions for A00-212

SAS Combo offer

Question 22: Which statement is true regarding the use of the PROC SQL step to query data that is stored in two or more tables?

1. When you join multiple tables, the tables must contain a common column.

2.  You must specify the table from which you want each column to be read.

3.  The tables that are being joined must be from the same type of data source.

4.  If two tables that are being joined contain a same-named column, then you must specify the table from which you want the column to be read.

Correct Answer : 4

Exp : Joining Tables
When multiple tables, views, or query-expressions are listed in the FROM clause, they are processed to form one table. The resulting table contains data from each contributing table. These queries are referred to as joins.
Conceptually, when two tables are specified, each row of table A is matched with all the rows of table B to produce an internal or intermediate table. The number of rows in the intermediate table (Cartesian product) is equal to the product of the number of rows in each of the source tables. The intermediate table becomes the input to the rest of the query in which some of its rows can be eliminated by the WHERE clause or summarized by a summary function.
A common type of join is an equijoin, in which the values from a column in the first table must equal the values of a column in the second table.

If you are joining two tables that contain a same-named column, then you must use a prefix to specify the table(s) from which you want the column to be read. Remember that if you join tables that don’t contain columns that have matching data values, you can produce a huge amount of output. Be sure to specify a WHERE clause to select only the rows that you want.

490 SAS Base Certificarion Practice Questions for A00-211  | 356+ SAS Advanced Certification Practice Questions for A00-212

SAS Combo offer

Question 21 : If you specify a CREATE TABLE statement in your PROC SQL step,

490 SAS Base Certificarion Practice Questions for A00-211  | 356+ SAS Advanced Certification Practice Questions for A00-212

SAS Combo offer

Question 21 : If you specify a CREATE TABLE statement in your PROC SQL step,

1.  the results of the query are displayed, and a new table is created.
2.  a new table is created, but it does not contain any summarization that was specified in the PROC SQL step.

3. a new table is created, but no report is displayed.

4.  results are grouped by the value of the summarized column.

Correct Answer : 3

Exp : Creating a Table without Rows
[1] The first form of the CREATE TABLE statement creates tables that automatically map SQL data types to tables that are supported by SAS. Use this form when you want to create a new table with columns that are not present in existing tables. It is also useful if you are running SQL statements from an SQL application in another SQL-based database.

[2] The second form uses a LIKE clause to create a table that has the same column names and column attributes as another table. To drop any columns in the new table, you can specify the DROP= data set option in the CREATE TABLE statement. The specified columns are dropped when the table is created. Indexes are not copied to the new table.

Both of these forms create a table without rows. You can use an INSERT statement to add rows. Use an ALTER TABLE statement to modify column attributes or to add or drop columns.

Creating a Table from a Query Expression
[3] The third form of the CREATE TABLE statement stores the results of any query-expression in a table and does not display the output. It is a convenient way to create temporary tables that are subsets or supersets of other tables.

When you use this form, a table is physically created as the statement is executed. The newly created table does not reflect subsequent changes in the underlying tables (in the query-expression). If you want to continually access the most current data, then create a view from the query expression instead of a table

The CREATE TABLE statement enables you to store your results in a SAS table instead of displaying the query results as a report.

490 SAS Base Certificarion Practice Questions for A00-211  | 356+ SAS Advanced Certification Practice Questions for A00-212

SAS Combo offer

Question 20: The following SAS program is submitted: data new (bufsize = 6144 bufno = 4); set old; run; Which one of the following describes the difference between the usage of BUFSIZE= and BUFNO= options?

490 SAS Base Certificarion Practice Questions for A00-211  | 356+ SAS Advanced Certification Practice Questions for A00-212

SAS Combo offer

Question 20: The following SAS program is submitted:
data new (bufsize = 6144 bufno = 4);
set old;
run;
Which one of the following describes the difference between the usage of BUFSIZE= and BUFNO= options?

1.  BUFSIZE= specifies the size of the input buffer in bytes; BUFNO= specifies the number of input buffers.

2. BUFSIZE= specifies the size of the output buffer in bytes; BUFNO= specifies the number of output buffers.

3.  BUFSIZE= specifies the size of the output buffer in kilobytes; BUFNO= specifies the number of input buffers.

4.  BUFSIZE= specifies the size of the output buffer in kilobytes; BUFNO= specifies the number of output buffers.

Correct Answer : 2

Exp : BUFSIZE= Data Set Option

Specifies the size of a permanent buffer page for an output SAS data set.
The page size is the amount of data that can be transferred for a single I/O operation to one buffer. The page size is a permanent attribute of the data set and is used when the data set is processed.
A larger page size can speed up execution time by reducing the number of times SAS has to read from or write to the storage medium. However, the improvement in execution time comes at the cost of increased memory consumption.
To change the page size, use a DATA step to copy the data set and either specify a new page or use the SAS default. To reset the page size to the default value in your operating environment, use BUFSIZE=0.
Note: If you use the COPY procedure to copy a data set to another library that is allocated with a different engine, the specified page size of the data set is not retained. [cautionend]
Operating Environment Information: The default value for BUFSIZE= is determined by your operating environment and is set to optimize sequential access. To improve performance for direct (random) access, you should change the value for BUFSIZE=. For the default setting and possible settings for direct access, see the BUFSIZE= data set option in the SAS documentation for your operating environment.

BUFNO= Data Set Option
Specifies the number of buffers to be allocated for processing a SAS data set.

The buffer number is not a permanent attribute of the data set; it is valid only for the current SAS session or job.
BUFNO= applies to SAS data sets that are opened for input, output, or update.
A larger number of buffers can speed up execution time by limiting the number of input and output (I/O) operations that are required for a particular SAS data set. However, the improvement in execution time comes at the expense of increased memory consumption.
To reduce I/O operations on a small data set as well as speed execution time, allocate one buffer for each page of data to be processed. This technique is most effective if you read the same observations several times during processing.
Operating Environment Information: The default value for BUFNO= is determined by your operating environment and is set to optimize sequential access. To improve performance for direct (random) access, you should change the value for BUFNO=. For the default setting and possible settings for direct access, see the BUFNO= data set option in the SAS documentation for your operating environment.

490 SAS Base Certificarion Practice Questions for A00-211  | 356+ SAS Advanced Certification Practice Questions for A00-212

SAS Combo offer

Question 19 : What happens if you use a GROUP BY clause in a PROC SQL step without a summary function?

490 SAS Base Certificarion Practice Questions for A00-211  | 356+ SAS Advanced Certification Practice Questions for A00-212

SAS Combo offer

Question 19 : What happens if you use a GROUP BY clause in a PROC SQL step without a summary function?

1.  The step does not execute.

2.  The first numeric column is summed by default.

3.  The GROUP BY clause is changed to an ORDER BY clause.

4.  The step executes but does not group or sort data.

Correct Answer : 3

Exp : GROUP BY Clause
The GROUP BY clause enables you to break query results into subsets of rows. When you use the GROUP BY clause, you use an aggregate function in the SELECT clause or a HAVING clause to instruct PROC SQL how to group the data. For details about aggregate functions, see Summarizing Data. PROC SQL calculates the aggregate function separately for each group. When you do not use an aggregate function, PROC SQL treats the GROUP BY clause as if it were an ORDER BY clause, and any aggregate functions are applied to the entire table.
The following query uses the SUM function to list the total population of each continent. The GROUP BY clause groups the countries by continent, and the ORDER BY clause puts the continents in alphabetical order:
select Continent, sum(Population)
from sql.countries
group by Continent
order by Continent;

The GROUP BY clause is used in queries that include one or more summary functions. If you specify a GROUP BY clause in a query that does not contain a summary function, your clause is changed to an ORDER BY clause.

490 SAS Base Certificarion Practice Questions for A00-211  | 356+ SAS Advanced Certification Practice Questions for A00-212

SAS Combo offer