Exercise 6 This exercise is based upon Chapter 6 of the SAS Advanced Certification Prep Guide. We will simply create some indices and check their performance against each other, and against SAS’s default choices. Begin by importing the Bacterial data set from Excel into WORK.WQ, and then save a copy that will have no indices: proc sql; create table wqnix as select * from wq; quit; We will next create a series of indices. The column RECORD is unique, so we should use the following command to create an index based on RECORD: proc sql; create unique index record on wq(record); quit; Create another index using the variable FECALREMARK. Now plot the variable FECALREMARK using VBAR in PROC SGPLOT. Should this variable be recommended for an index? Why or why not? We should be able to create a near-unique index using STATION and COLLECTION_DATE composite whose performance can be compared to RECORD. proc sql; create index sitedate on wq(station, collection_date); quit; Graduate students: Create a third table, WQSORT, by sorting WQ on STATION and COLLECTION_DATE, and create a composite index for that table identical to the one above. We can now compare some sorts for the following query (the table name WQ will change as you change tables). proc sql; 'B%' select * from wq where station like and fecalcoli gt 200 'GT'); and fecalremark in('EST', quit; Record Real Time, CPU Time and any notes in the SAS LOG when running the above query on the following (one or more of these choices may not produce a result). Summarize your results. WQ with default query. Was an index selected? Which one? WQSORT (graduate students) WQNIX WQ with RECORD index
Exercise 6 This exercise is based upon Chapter 6 of the SAS Advanced Certification Prep Guide. We will simply create some indices and check their performance against each other, and against SAS’s default choices. Begin by importing the Bacterial data set from Excel into WORK.WQ, and then save a copy that will have no indices: proc sql; create table wqnix as select * from wq; quit; We will next create a series of indices. The column RECORD is unique, so we should use the following command to create an index based on RECORD: proc sql; create unique index record on wq(record); quit; Create another index using the variable FECALREMARK. Now plot the variable FECALREMARK using VBAR in PROC SGPLOT. Should this variable be recommended for an index? Why or why not? We should be able to create a near-unique composite index using STATION and COLLECTION_DATE whose performance can be compared to RECORD. proc sql; create index sitedate on wq(station, collection_date); quit; Graduate students: Create a third table, WQSORT, by sorting WQ on STATION and COLLECTION_DATE, and create a composite index for that table identical to the one above. We can now compare some sorts for the following query (the table name WQ will change as you change tables). proc sql; select * from wq where station like 'B%' and fecalcoli gt 200 and fecalremark in('EST', 'GT'); quit; Record Real Time, CPU Time and any notes in the SAS LOG when running the above query on the following (one or more of these choices may not produce a result). Summarize your results. WQ with default query. Was an index selected? Which one? WQSORT (graduate students) WQNIX WQ with RECORD index
CE_6.docx