SAS Training Self-Assessment

SAS SQL 2: Processing Data Efficiently in Real-World Scenarios

Select the best answer for each question. When you are finished, click Submit Quiz.

  1. Which statement successfully assigns a library reference to the SAS data library?

     a.   libname 'c:myclass\data';
     b.   libname mydata c:\myclass\data;
     c.   libname 'c\myclass\data mydata;
     d.   libname mydata 'c:\myclass\data';


  2. Which step boundary terminates a PROC SQL step?

     a.   run;
     b.   end;
     c.   quit;
     d.   done;


  3. The following SELECT statement assigns a format to the Hire_Date column:

      
    select hire_date format=mmddyy10.


    Which statement formats Hire_Date equivalently in a PROC PRINT step?

     a.   format hire_date=mmddyy10;
     b.   format hire_date mmddyy10.;
     c.   format mmddyy10.=hire_date;
     d.   format hire_date format_mmddyy10;


  4. Which sentence best describes how an in-line view is different from a subquery?

     a.   An in-line view is the same as a subquery.
     b.   An in-line view is used in a HAVING or WHERE clause.
     c.   An in-line view is used in the FROM clause in the place of a table name.
     d.   An in-line view can have only one column specified and can return only a single value.


  5. Which set operator does not, by default, overlay columns based on position and eliminate duplicate rows?

     a.   LEFT
     b.   EXCEPT
     c.   INTERSECT
     d.   OUTER UNION


  6. You have been asked to join the work.customer and work.orders tables. The join column in both tables is ID. How would you reference the columns in your WHERE clause join criteria?

     a.   where ID=ID
     b.   where ID=C_ID
     c.   where customer.ID=orders.ID
     d.   where work.customer_ID=work.orders_ID


  7. Which SQL query returns only the rows with the % sign in the second position of the variable SaleDiscount?

     a.  
    select SaleDiscount
       from discount
       where SaleDiscount like '_%#' escape '#';
     b.  
    select SaleDiscount
       from discount
       where SaleDiscount like '_#%' escape '#';
     c.  
    select SaleDiscount
       from discount
       where SaleDiscount like '_%';
     d.  
    select SaleDiscount
       from discount
       where SaleDiscount like '_#' escape '#';


  8. Consider the following PROC SQL step that creates an index.

      
        proc sql;
           create index city
                  on work.sales (city, state);
        quit;

    What will happen when the code is executed?

     a.   A simple index CITY is created on the work.sales table.
     b.   A composite index CITY is created on the work.sales table.
     c.   A simple index and a composite index are created on the work.sales table.
     d.  
    An error occurs because a composite index cannot have the same name as a column that exists in the table.


  9. When you perform an inner join without using a WHERE clause, what is the likely outcome of the query?

     a.   The tables are match merged.
     b.   Nothing. You get an error message.
     c.   The result is the Cartesian product, matching all rows from all of the tables in the FROM clause.
     d.   The tables are matched automatically by the column in common to all tables listed in the FROM clause.


  10. Which SQL query returns the desired results from work.table_1?

    work.table_1
    A
    B
    C
    1 1
    1
    1 2
    2
    2 1  
    2 2
    1


    Desired Output
    A
    B
    C
    1 1
    1
    1 2
    2
    2 2
    1

     a.  
    select *
       from table_1
       where sum(B,C) > A+C;
     b.  
    select *
       from table_1
       where B+C > sum(A,C);
     c.  
    select *
       from table_1
       where B+C and sum(A,C);
     d.  
    select *
       from table_1
       where B+C or sum(A,C);


  11. Which sentence most accurately defines a view?

     a.   A view is a query nested within a query.
     b.   A view is a table with more rows than columns.
     c.   A view is a stored query with instructions to access the data. It contains no physical rows of data.
     d.   A view is a set of instructions that is stored in memory. Each time the instructions are accssed in memory, the rows are returned to create a physical table with physical rows and columns.


  12. Which SQL statement correctly displays the descriptor portion of a table?

     a.   describe as work.promo;
     b.   describe table work.promo;
     c.   describe dictionary.tables;
     d.   describe contents work.promo;


  13. Consider the table below, which contains the values for Job_Title.

    Job_Title
    Job_Level
    Employee Recruiter I Level 1
    Payroll Specialist Other

    Which SQL expression correctly creates the new Job_Level column?

     a.  
    case scan(Job_Title,-1)
         when 'I' then 'Level 1'
         else 'Other'
    end as Job_Level
     b.  
    case scan(Job_Title,-1)
         when 'I' then 'Level 1'
         else 'Other'
    as Job_Level
     c.  
    if substr(Job_Title,-1)='X' then 'Level 1'
       else 'Other'
    end as Job_Level
     d.  
    case substr(Job_Title,-1)
         when 'I' then 'Level 1'
         else 'Other'
    end as Job_Level


  14. Which PROC SQL option is useful for checking the syntax of any SQL statement without executing the statement?

     a.   EXEC
     b.   NOEXEC
     c.   VALIDATE
     d.   FEEDBACK


  15. According to the SAS table shown below, which SQL statement creates a copy of the table and adds a new character column that displays the instructor's name as first name followed by last name with a blank space in between?

    Work.Schedule
    A
    Name
    LWPRG1 Hollis, Dr. George
    LWPRG2 Wickam, Dr. Alice
    LWPRG3 Forest, Dr. Peter

     a.  
    create table work.NewNames
       select *,
              catx(' ',scan(Name,2,','),
              scan(Name,1,',')) as InstrName
          from work.Schedule
     b.  
    create table work.NewNames(drop=Name) as
       select *,
              catx(' ',scan(Name,2,','),
              scan(Name,1,',')) as InstrName
          from work.Schedule;
     c.  
    create table work.NewNames(drop=Name) as
       select *,
              InstrName=catx(' ',scan(Name,2,','),
              scan(Name,1,',')) 
          from work.Schedule;
     d.  
    create table work.NewNames as
       select *,
              catx(' ',scan(Name,2,','),
              scan(Name,1,',')) as InstrName
          from work.Schedule;


  16. Given the following symbol table, which macro program produces a listing report for each of the data set names stored in Table 1 through Table 3?

    Global Symbol Table
    TABLE1 First
    TABLE2 Second
    TABLE3 Third
    N 3

     a.  
    %macro test;
       %do i=1 %to n;
          proc sql;
             select * from &&table&i;
          quit;
       %end;
    %mend;
     b.  
    %macro test;
       %do i=1 %to &n;
          proc sql;
             select * from &&table&i;
          quit;
       %end;
    %mend;
     c.  
    %macro test;
       %do i=1 %to n;
          proc sql;
             select * from &table&i;
          quit;
       %end;
    %mend;
     d.  
    %macro test;
       %do i=1 %to &n;
          proc sql;
             select * from &table&i;
          quit;
       %end;
    %mend;


  17. Which example defines a macro named Report with correctly implemented parameters named var and dsn?

     a.  
    %macro Report(&var, &dsn);
       title "From &dsn";
       proc sql;
          select sum(&var) "Total &var"
          from &dsn;
       quit;
    %mend Report;
     b.  
    %macro Report("var", "dsn");
       title "From &dsn";
       proc sql;
          select sum(&var) "Total &var"
          from &dsn;
       quit;
    %mend Report;
     c.  
    %macro Report(var, dsn);
       proc sql;
          title "From &dsn";
          select sum(&var) "Total &var"
          from &dsn;
       quit;
    %mend Report;
     d.  
    %macro Report(:var, :dsn);
       title "From &dsn";
       proc sql;
          select sum(&var) "Total &var"
          from &dsn;
       quit;
    %mend Report;


  18. Assuming that you began your SAS session today, which statement correctly sets the macro variable currdate to today's date?

     a.   %let currdate=today();
     b.   %let currdate=%sysfunc(&sysdate);
     c.   %let currdate=put(today(),date9.);
     d.   %let currdate=%sysfunc(today(),date9.);


  19. Which example creates a sequence of macro variables, Table1 through Table21, containing the names of the tables in the ORION library?

     a.  
    proc sql;
       select memname
          into Table1-Table21
          from dictionary.tables
          where libname='ORION';
    quit;
     b.  
    proc sql;
       select memname
          into :Table1 to :Table21
          from dictionary.tables
          where libname='ORION';
    quit;
     c.  
    proc sql;
       select memname
          into Table21 separated by ','
          from dictionary.tables
          where libname='ORION';
    quit;
     d.  
    proc sql;
       select memname
          into :Table1-:Table21
          from dictionary.tables
          where libname='ORION';
    quit;


  20. Which example correctly creates a macro variable in a PROC SQL step?

     a.   call symputx("City_MPG",MPG_Total/CarCount);
     b.  
    select AVG(MPG_City)
       into :City_MPG
       from sashelp.cars;   
     c.   %let City_MPG=put(MPG_City/CarCount,best.);
     d.  
    select AVG(MPG_City)
       into City_MPG
       from sashelp.cars;