Select the best answer
for each question. After completing the quiz, check your answers using
the answer key in the appendix.
-
Which of the following
correctly creates a macro variable in a PROC SQL step?
-
proc sql noprint;
select avg(Days)
into:NumDays
from certadv.all;
quit;
%put &=NumDays;
-
proc sql noprint;
select avg(Days)
into NumDays
from certadv.all;
quit;
%put &=NumDays;
-
proc sql noprint;
select avg(Days) as NumDays
from certadv.all;
quit;
%put &=NumDays;
-
proc sql noprint;
select Days
into avg(Days) as NumDays
from certadv.all;
quit;
%put &=NumDays;
-
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 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;
-
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;
-
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;
-
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;
-
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;
-
into:CensusAvg2010 separated by ''
-
into CensusAvg2010 separated by ''
-
intoCensusAvg2010 trimmed
-
into:CensusAvg2010 trimmed
-
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?
-
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;
-
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;
-
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;
-
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;
-
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;
-
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;
-
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;
-
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;
-
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;
-
What is the FedSQL equivalent
of the following PROC SQL query?
proc sql inobs=5;
select *
from certadv.airports
order by ID;
quit;
-
libname certadv v9 'C:UsersStudentcertadv';
proc fedsql inobs=5;
select *
from certadv.airports
order by ID;
quit;
-
libname certadv v9 'C:UsersStudentcertadv';
proc fedsql limit 10;
select *
from certadv.airports
order by ID;
quit;
-
libname certadv v9 'C:UsersStudentcertadv';
proc fedsql;
select *
from certadv.airports
order by ID
limit=5;
quit;
-
libname certadv v9 'C:UsersStudentcertadv';
proc fedsql;
select*
from certadv.airports
order by ID
limit=5;
quit;