Quiz

Select the best answer for each question. After completing the quiz, check your answers using the answer key in the appendix.
  1. Which of the following correctly creates a macro variable in a PROC SQL step?
    1. proc sql noprint;
   select avg(Days)
      into:NumDays
      from certadv.all;
quit;
%put &=NumDays;
    2. proc sql noprint;
   select avg(Days)
      into NumDays
      from certadv.all;
quit;
%put &=NumDays;
    3. proc sql noprint;
   select avg(Days) as NumDays
      from certadv.all;
quit;
%put &=NumDays;
    4. proc sql noprint;
   select Days 
      into avg(Days) as NumDays
      from certadv.all;
quit;
%put &=NumDays;
  2. Suppose you are asked to create a report of the courses that are offered in all three available locations. The report should not contain any duplicate items. Which program correctly displays the following query result?
    PROC SQL Query Result
    1. proc sql noprint;
   select distinct strip(Location) format=$upcase8. 
                   as LocalList separated by ', '
      from certadv.schedule
      order by Location
;
quit;

%put &=LocalList;
footnote;
title "Courses Offered in &LocalList";
proc sql;
   select distinct Course_Code, Course_Title, Fee
      from certadv.all
      order by Course_Code;
quit;
    2. proc sql noprint;
   select distinct strip(Location) format=$upcase8. as Location
      into LocalList separatedby ', '
      from certadv.schedule
      order by Location
;
quit;

%put &=LocalList;
footnote;
title "Courses Offered in &LocalList";
proc sql;
   select distinct Course_Code, Course_Title, Fee
      from certadv.all
      order by Course_Code;
quit;
    3. proc sql noprint;
   select distinct strip(Location) format=$upcase8. as Location
      into:LocalList separated by ', '
      from certadv.schedule
      order by Location
;
quit;

%put &=LocalList;
footnote;
title "Courses Offered in &LocalList";
proc sql;
   select distinct Course_Code, Course_Title, Fee
      from certadv.all
      order by Course_Code;
quit;
    4. proc sql noprint;
   select distinct strip(Location) format=$upcase8. as Location
      into:LocalList separated by ', '
      from certadv.schedule
      order by Location
;
quit;

%put &=LocalList;
footnote;
title "Courses Offered in &LocalList";
proc sql;
   select distinct strip(Location) format=$upcase8. as Location,
                 Course_Code, Course_Title, Fee
      into:LocalList separated by ', '
      from certadv.all
     order by Course_Code;
quit;
  3. Complete the following SQL code to remove leading and trailing blanks when storing the value of the macro variable CensusAvg2010. 
    proc sql noprint;
   select avg(Census_Apr2010),
      ___________________________________
      from certadv.census
;
quit;
%put &=CensusAvg2010;
    1. into:CensusAvg2010 separated by ''
    2. into CensusAvg2010 separated by ''
    3. intoCensusAvg2010 trimmed
    4. into:CensusAvg2010 trimmed
  4. Suppose you are asked to concatenate the list of instructors who teach at the Dallas location and suppose also that the begin date for the course is after 01JAN2020. The list of instructors should be stored in a macro variable and should be separated by a comma with no leading or trailing blanks. Which SQL program would correctly accomplish the task?
    1. proc sql noprint;
   select distinct Teacher format=$upcase21.
      into:NameListDallas separated by ','
      from certadv.schedule
      where Location='Dallas' and Begin_Date>'01JAN2020'd;
quit;
%put &=NameListDallas;
    2. proc sql noprint;
   select distinct Teacher format=$upcase21.
      into:NameListDallas separated by ',' trimmed
      from certadv.schedule
      where Location='Dallas' and Begin_Date>'01JAN2020'd;
quit;
%put &=NameListDallas;
    3. proc sql noprint;
   select distinct Teacher format=$upcase21.,
      into NameListDallas separated by ','
      from certadv.schedule
      where Location='Dallas' and Begin_Date>'01JAN2020'd;
quit;
%put &=NameListDallas;
    4. proc sql noprint;
   select distinct Teacher format=$upcase21. as NameListDallas
      separated by ',' trimmed
      from certadv.schedule
      where Location='Dallas' and Begin_Date>'01JAN2020'd;
quit;
%put &=NameListDallas;
  5. What is the FedSQL equivalent of the following PROC SQL query?
    proc sql;
   select State, 
          Census_Apr2010 format=comma12., 
          PopEst_Apr2018 format=comma12.,
          (PopEst_Apr2018-Census_Apr2010) format=comma12. as PopChange
      from certadv.census
      where Census_Apr2010>PopEst_Apr2018
      order by State;
quit;
    1. libname certadv v9 'C:UsersStudentcertadv';
proc fedsql;
   select State, 
          Census_Apr2010 format=comma12., 
          PopEst_Apr2018 format=comma12.,
          (PopEst_Apr2018-Census_Apr2010) format=comma12. as PopChange
      from certadv.census
      where Census_Apr2010>PopEst_Apr2018
      order by State;
quit;
    2. libname certadv v9 'C:UsersStudentcertadv';
proc fedsql;
   select     State, 
          put(Census_Apr2010, comma12.) as Census_Apr2010, 
          put(PopEst_Apr2018, comma12.) as PopEst_Apr2018,
          put(PopEst_Apr2018-Census_Apr2010, comma12.) as PopChange
   from certadv.census
   where Census_Apr2010>PopEst_Apr2018
   order by state;
quit;
    3. libname certadv v9 'C:UsersStudentcertadv';
proc fedsql;
   select State,
          Census_Apr2010,
          PopEst_Apr2018,
          (PopEst_Apr2018-Census_Apr2010) as PopChange
      from certadv.census
      where Census_Apr2010>PopEst_Apr2018
      order by State;
quit;
    4. libname certadv v9 'C:UsersStudentcertadv';
proc fedsql;
   select State,
          Census_Apr2010,
          PopEst_Apr2018,
          (PopEst_Apr2018-Census_Apr2010) as PopChange
      from certadv.census
      where Census_Apr2010>PopEst_Apr2018
      order by State;
      format Census_Apr2010 PopEst_Apr2018 PopChange comma8.
quit;
  6. What is the FedSQL equivalent of the following PROC SQL query?
    proc sql inobs=5;
   select *
      from certadv.airports
      order by ID;
quit;
    1. libname certadv v9 'C:UsersStudentcertadv';
proc fedsql inobs=5;
   select *
      from certadv.airports
      order by ID;
quit;
    2. libname certadv v9 'C:UsersStudentcertadv';
proc fedsql limit 10;
   select *
      from certadv.airports
      order by ID;
quit;
    3. libname certadv v9 'C:UsersStudentcertadv';
proc fedsql;
   select *
      from certadv.airports
      order by ID
      limit=5;
quit;
    4. libname certadv v9 'C:UsersStudentcertadv';
proc fedsql;
   select*
      from certadv.airports
      order by ID
      limit=5;
quit;
Last updated: October 16, 2019
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.222.184.126