test0

SAS® Certification: Advanced Programming Practice Exam Certification Practice Exam Select the best answer for each question and click Score My Practice Exam. 1. Given the following SAS data sets One and Two: The following SAS program is submitted: proc sql; select one.*, sales from one, two where one.year=two.year and one.qtr=two.qtr; quit; Which of the following reports is generated? You should read the introductory material before you take this practice exam. One Year Qtr Budget 2001 3 500 2001 4 400 2002 1 700 Two Year Qtr Sales 2001 4 300 2002 1 600 a. Year Qtr Budget Sales 2001 3 500 300 2002 1 700 600 b. Year Qtr Budget Sales 2001 4 400 300 2002 1 700 600 c. Year Qtr Budget Sales 2001 3 500 . 2001 4 400 300 2002 1 700 600 d. Year Qtr Budget Sales 2001 3 500 300 2001 4 400 300 2. Given the following SAS data sets One and Two: The following SAS program is submitted: proc sql; select one.*, sales from one right join two on one.year=two.year; quit; Which one of the following reports is generated? 3. Which one of the following programs is syntactically correct? 2002 1 700 300 2001 3 500 600 2001 4 400 600 2002 1 700 600 One Year Qtr Budget 2001 3 500 2001 4 400 2002 1 700 Two Year Qtr Sales 2001 4 300 2002 1 600 a. Year Qtr Budget Sales 2001 3 500 . b. Year Qtr Budget Sales 2001 4 400 300 2002 1 700 600 c. Year Qtr Budget Sales 2001 3 500 . 2001 4 400 300 2002 1 700 600 d. Year Qtr Budget Sales 2001 3 500 300 2001 4 400 300 2002 1 700 600 a. proc sql; create table forecast as select a.*, b.sales from actual a, budget b where a.dept=b.dept and 4. Given the following SAS data sets One and Two: The following SAS program is submitted: proc sql; select * from one except select * from two; quit; Which of the following reports is generated? a.month=b.month; quit; b. proc sql; create table forecast as select a.* b.sales from actual as a, budget as b where a.dept=b.dept and a.month=b.month; quit; c. proc sql; create table forecast as select a.*, b.sales from actual as a, budget as b where dept=dept and month=month; quit; d. proc sql; create table forecast as select a.*, b.sales from actual, budget where a.dept=b.dept and a.month=b.month; quit; One Num Var1 1 A 1 A 2 B 3 C Two Num Var2 1 A 4 Y 4 Z a. Num 2 3 b. Num Var1 2 B 5. Given the following SAS data sets One and Two: The following SAS program is submitted: proc sql; select * from one intersect all select * from two; quit; Which of the following reports is generated? 3 C c. Num Var1 1 A 2 B 3 C d. Num Var2 1 A 4 Y 4 Z One Num Var1 1 A 1 A 2 B 3 C Two Num Var2 1 A 4 Y 4 Z a. Num 1 b. Num 1 2 c. Num Var1 1 A d. Num Var1 1 A 1 A 6. Given the following SAS data sets One and Two: The following SAS program is submitted: proc sql; select product from one where exists (select * from two where one.num=two.num); quit; Which one of the following reports is generated? 7. How many columns can an SQL procedure subquery within a WHERE or HAVING clause return to the outer query? One Num Product 1 Computer 2 Printer 3 Monitor 4 Keyboard 5 Modem Two Num Product 1 Portable 5 External a. Product Computer Modem b. Product Computer Printer c. Product Printer Monitor Keyboard d. Product Computer Printer Monitor Keyboard Modem a. 0 8. Given the following SAS data sets One and Two: The following SAS program is submitted creating the output table Three: data three; merge one (in=in1) two (in=in2); by num; if in2; run; Which of the following SQL programs creates an equivalent SAS data set Three? b. 1 c. 2 d. the same number of columns that are in the table One Num Char1 1 A 2 B 4 D Two Num Char2 2 X 3 Y 5 V Three Num Char1 Char2 2 B X 3 Y 5 V a. proc sql; create table three as select two.num, char1, char2 from one left join two on one.num=two.num; quit; b. proc sql; create table three as select two.num, char1, char2 from one right join two on one.num=two.num; quit; c. proc sql; create table three as select two.num, char1, char2 from one full join two on one.num=two.num; quit; d. proc sql; create table three as select two.num, char1, char2 9. Table One has five million observations. Table Two has one thousand observations. These tables have identical column attributes. Concatenating tables One and Two should result in 5,001,000 observations. Which one of the following SAS techniques uses the least CPU time and I/O operations to process? 10. The following SAS program is submitted: proc sql noprint; select name into : info separated by ' ' from dictionary.columns where libname="SASHELP" and memname="CLASS"; quit; Given that a SAS data set Sashelp.Class exists, which one of the following is generated by the above program? 11. The following SAS program is submitted: %let lib=SASUSER; %let dsn=CLASS; proc sql; select name from dictionary.columns where libname="&lib" and quit; Which one of the following conditions completes the WHERE clause to create a subset from the data set Dictionary.Columns about Sasuser.Class? from one, two where one.num=two.num; quit; a. the APPEND procedure b. the SET statement in the DATA step c. the INSERT INTO statement in the SQL procedure d. the OUTER UNION CORR operator in the SQL procedure a. a list of names b. a syntax error in the log c. a macro variable called info d. a report showing metadata information a. table="&dsn" b. name="&dsn" 12. Which one of the following statements is true about an SQL procedure view? 13. The following SAS program is submitted: proc sql noprint; select name into : name1 -: name19 from one; quit; Given that table One has 19 observations and 5 variables, how many macro variables are created by the above program? 14. Which one of the following SAS programs removes the index Jobcode from the table Staff? 15. A batch (non -interactive) job is submitted on Wednesday, 20 January 1999 (SAS date 14264). The program completes the following day. The following code is the last step in the SAS program: data _null_; call symput('mdate', input("&sysdate", date7.)); c. member="&dsn" d. memname="&dsn" a. It stores large amounts of data. b. It updates data from multiple database tables. c. It contains both the data and the descriptor portions. d. It accesses the most current data in changing tables. a. 5 b. 6 c. 19 d. 20 a. proc sql; drop index jobcode from work.staff; quit; b. proc sql; delete index jobcode from work.staff; quit; c. proc sql; drop index from work.staff; quit; d. proc sql; delete index from work.staff; quit; run; Which of the following is the value of the macro variable mdate? 16. Given the following SAS data set Mylib.Mydata: The following SAS program is submitted: data _null_; set mylib.mydata; call symput('animal' || left(_n_), name); run; %let i=2; title "The value is &&animal&i"; Which one of the following does the TITLE statement resolve to? 17. The following SAS program is submitted: %let sun=SHINE; %let shine=MOON; proc print data=weather(keep=sun shine moon); sum &&&&sun; run; Which one of the following variables is summed in the resulting report? a. 14264 b. 14265 c. 20JAN99 d. 21JAN99 Mylib.Mydata Name Animal Age Max Cat 9 Brown Dog 22 Large Pig 1 a. The value is Dog b. The value is animal2 c. The value is Brown d. The value is &animal2 a. sun b. MOON c. SHINE 18. The following SAS program is submitted: %let code=set old (keep=A B); Newvar=a || b; run; data new; &code run; proc print data=new; run; Which one of the following is the result of the above program? 19. The following SAS program is submitted: data towns; input City $ pop year; cards; ANDOVER 75000 1998 CARY 80000 1998 ANDOVER 14000 1977 CARY 71000 1986 ; %macro a(location); data a; set towns; %if &location=UK %then %do; where city='ANDOVER'; %end; %if &location=NC %then %do; where city='CARY'; %end; if pop>70000; run; %mend; %a(UK) Which one of the following represents the resulting output in the SAS data set A? d. None, an ERROR message will be written to the log. a. There will be errors in the log and no report. b. There will be no report and no errors in the log. c. There will be a report that has only the variables A and B. d. There will be a report that has the variables A, B, and Newvar. a. 1 observation with the value of CARY for the variable City. b. 2 observations with the value of CARY for the variable City. c. 1 observation with the value of ANDOVER for the variable City. 20. The following SAS program is submitted: %let a=cat; %macro animal; %let a=dog; %mend; %animal %put a is &a; Which one of the following is written to the SAS log? 21. The following SAS program is submitted at the start of a new SAS session: %macro trans; %let type=Airplane; proc print data=sasuser.activities; where trans="&type"; run; %location(Automobile) %put type is &type; %mend; %macro location(type); data _null_; call symput('type', 'Train'); run; %mend; %trans Which one of the following is written to the log by the %PUT statement? 22. The following SAS program is submitted: %let day=Tuesday; %macro loop(day); %do day=2 %to 6; proc print data=sasuser.houses; where sellday="&day"; d. 2 observations with the value of ANDOVER for the variable City. a. a is b. a is &a c. a is cat d. a is dog a. type is &type b. type is Train c. type is Airplane d. type is Automobile run; %end; %put day is &day; %mend; %loop(Friday) Which one of the following is written to the log by the %PUT statement? 23. Which of the following SAS functions retrieves the value of a macro variable during DATA step execution? 24. The following SAS program is submitted: %let type=RANCH; proc sql; create view houses as select * from sasuser.houses where style="&type"; quit; %let type=CONDO; proc print data=houses; run; The report that is produced displays observations whose value of Style are all equal to RANCH. Which one of the following functions on the WHERE clause resolves the current value of the macro variable type? 25. Which one of the following is the purpose of the %STR function? a. day is 7 b. day is &day c. day is Friday d. day is Tuesday a. GET b. SYMGET c. %SYMGET d. &MVARNAME a. GET b. SYMGET c. %SYMGET d. &RETRIEVE a. to extract part of a macro character value b. to treat semicolons and other selected tokens as text 26. The following SAS program is submitted: %let mvar=bye; data _null_; var1='hi'; var2='hello'; call symput('var1' || "&mvar", var2); run; Which one of the following is the name of the macro variable created by the CALL SYMPUT statement? 27. Given the following partial SAS log: Which of the following SAS system options can be used to display the results of resolving the macro variable reference &location? 28. Which one of the following statements displays all user -defined macro variables in the SAS log? c. to concatenate the values of two macro variables into one d. to extract part of a macro variable based on matching criteria a. var1 b. hibye c. var1bye d. No macro variable is created because the CALL SYMPUT statement is not valid. 11 %macro a(location); 12 %if &location = FL %then 13 libname &location 'c:\state'; 14 %mend; 15 %a(FL) NOTE: SCL source line. 16 libname mylib 'c:\mydata'; ------- 22 ERROR: Libname FL is not assigned. ERROR: Error in the LIBNAME statement. ERROR 22-7: Invalid option name LIBNAME. a. MPRINT only b. MLOGIC only c. SYMBOLGEN only d. MPRINT and MLOGIC a. %put user=; b. 29. Given the following SAS log: Which one of the following statements was used in the code shown above to remove the macro variable dsn from the symbol table? 30. When using the KEY= option in the SET statement in the DATA step, which one of the following is the purpose of the automatic variable _IORC_? 31. Which one of the following statements is true regarding the TRANSPOSE procedure? 32. Which one of the following is true with respect to when a DATA step with a MERGE statement successfully combines data in SAS? %put user; c. %put _user_; d. options mprint 55 %let lib = sasuser.; 56 %let dsn = class; 57 %put dsn is &dsn, and lib is &lib; dsn is class, and lib is sasuser. 58 59 %put dsn is &dsn, and lib is &lib; WARNING: Apparent symbolic reference DSN not resolved. dsn is &dsn, and lib is sasuser. a. %SYMDEL dsn; b. %REMOVE dsn; c. %DELETE dsn; d. %MACDEL dsn; a. to determine the amount of I/O usage b. to determine the success of the I/O operation c. to determine the length of the master observation d. to determine the location of the master observation a. The TRANSPOSE procedure produces printed output by default. b. The TRANSPOSE procedure rearranges all variables by default. c. The TRANSPOSE procedure does not create an output data set by default. d. The TRANSPOSE procedure creates one observation for each restructured variable for each BY group. a. when the data sources are indexed SAS data sets b. when the data sources are unsorted raw data files c. when the data sources are unsorted, unindexed SAS data sets 33. Which one of the following statements is true regarding compressed SAS data sets? 34. Which one of the following statements is true regarding the use of a SAS numeric variable of less than 8 bytes? 35. Which one of the following programs creates a SAS data view named Sasuser.Ranch? 36. By default, in which one of the following SAS catalogs are user -defined formats stored? 37. Which one of the following FORMAT procedure options is used to create a format from the values in a SAS data set? d. when not all the data sources contain the same BY variable(s) a. Deleted observation space is reused by default. b. Observations are addressable by observation number. c. More input operations are necessary to read from the data set during processing. d. More disk space is usually required to store compressed data sets than to store uncompressed data sets. a. It requires no additional processing when reading the SAS data set. b. It might reduce the precision when the value is written to the SAS data set. c. It has an effect on how the values are stored in the program data vector (PDV). d. It has no effect on the precision of the largest number that can be accurately stored. a. data _null_ view=sasuser.ranch; set sasuser.houses; where style='RANCH'; run; b. data _null_ / view=sasuser.ranch; set sasuser.houses; where style='RANCH'; run; c. data sasuser.ranch view=sasuser.ranch; set sasuser.houses; where style='RANCH'; run; d. data sasuser.ranch / view=sasuser.ranch; set sasuser.houses; where style='RANCH'; run; a. Work.Format b. Work.Formats c. Sasuser.Format d. Sasuser.Formats 38. Which one of the following FORMAT procedure options is used to create a SAS data set from an existing format? 39. Which one of the following represents the resource(s) saved by the BUFSIZE= and BUFNO= options? 40. Which one of the following represents the resource(s) conserved by the SASFILE statement if the entire SAS data set fits in memory? 41. Which one of the following represents where the input is measured when using SAS data sets as input to a DATA step? 42. The SAS data set One consists of five million observations and has 25 variables. Which one of the following SAS programs requires the least CPU time to be processed? a. FMTLIB b. INVALUE c. CNTLIN= d. CNTLOUT= a. FMTLIB b. CREATE c. CNTLIN= d. CNTLOUT= a. I/O only b. CPU only c. disk space only d. I/O, CPU, and disk space a. I/O only b. CPU only c. memory only d. CPU and I/O a. between the storage on disk and utility swap files b. between the storage on disk and the input buffers c. between the input buffers and the program data vector (PDV) d. between the program data vector (PDV) and the output buffers a. data two; set one; if year(date)=1999 and state in ('NY' 'NJ' 'CT') then 43. The SAS data set Temp contains 1,000 observations and 10 variables. Which one of the following SAS DATA steps writes the variables A, B, and C to both SAS data sets One and Two? sales=sales98*1.1; else if year(date)=2000 and state in ('NY' 'NJ' 'CT') then sales=sales98*1.15; else if year(date)=2001 and state in ('NY' 'NJ' 'CT') then sales=sales98*1.2; run; b. data two; set one; if year(date)=1999 and state in ('NY' 'NJ' 'CT') then sales=sales98*1.1; if year(date)=2000 and state in ('NY' 'NJ' 'CT') then sales=sales98*1.15; if year(date)=2001 and state in ('NY' 'NJ' 'CT') then sales=sales98*1.2; run; c. data two; set one; if state in ('NY' 'NJ' 'CT')then do; year=year(date); if year=1999 and state in('NY' 'NJ' 'CT') then sales=sales98*1.1; if year=2000 and state in('NY' 'NJ' 'CT') then sales=sales98*1.15; if year=2001 and state in('NY' 'NJ' 'CT') then sales=sales98*1.2 end; run; d. data two; set one; if state in('NY' 'NJ' 'CT') then do; year=year(date); select(year); when(1999) sales=sales98*1.1; when(2000) sales=sales98*1.15; when(2001) sales=sales98*1.2; otherwise; end; end; run; a. data one two(keep=a b c); set temp(keep=a b c d); run; b. data one two; set temp(keep=a b c); run; c. data one(keep=a b c) two; set temp(keep=a b c d); 44.