150 likes | 259 Views
Multidimensional Arrays Used for a Quality Control Process. Presented by Christine Davies 02.22.2012. Outline. What does the data look like? Identifying the QC check How to perform the check in SAS? How to automate the check?. What does the data look like?.
E N D
Multidimensional Arrays Used for a Quality Control Process Presented by Christine Davies 02.22.2012
Outline • What does the data look like? • Identifying the QC check • How to perform the check in SAS? • How to automate the check?
What does the data look like? • All data is available for public use • ~100 tables of estimates for National Science Foundation • All tables are produced in Excel, from a SAS dataset. • Estimates – By Year, Gender & Major
What does the data look like? (cont.) • One main table – summary of enrollment for 3 majors • 3 supporting tables – one for each of the majors
Identifying the QC check • Two types of checks: • Within table checks • Gender Columns = Total Column • Between table checks • Major 1 Table + Major 2 + Major 3 Table = Summary Table
How to perform the check in SAS? • Arrays and metadata! • General form for the multidimensional ARRAY statement: ARRAY array-name {rows, columns} $ length elements (initial values); • Metadata – capture rows and columns needed for ARRAY statement.
How to perform the check in SAS? (cont.) • Putting data into an array: Data _1; set T1 end=eof; array _T1(25,3) _T1_1-_T1_75; array _(3) col_1-col_3; do i=1 to 3; _T1{_n_,i}=_{i}; end; if eof; keep _T1_1-_T1_75; • Resulting dataset: One row, 75 variables
How to perform the check in SAS? (cont.) • Program the within table QC check: data _null_; set _1; file txt; array _1(25,3) _1_1-_1_75; do i=1 to 25; if _1{i,1} ne sum(_1{i,2},_1{i,3}) then put "Total Column does not equal sum of Male and Female for row:" i; end;
How to perform the check in SAS? (cont.) • Checking between table relationships • Need all table data in their own array • 1 array for each major data _null_; set _1; if _n_=1 then set _2; if _n_=1 then set _3; if _n_=1 then set _4; file txt; array _1(25,3) _1_1-_1_75; array _2(25,3) _2_1-_2_75; array _3(25,3) _3_1-_3_75; array _4(25,3)) _4_1-_4_75;
How to perform the check in SAS? (cont.) • Program the between table QC check: ~ do i=1 to 25; do j=2; /*2 is the column for males*/ if _1{i,j} ne sum(_2{i,j},_3{i,j},_4{i,j}) then put "row = " i " column = " j " _1{i,j} ne sum(_2{i,j},_3{i,j},_4{i,j})"; end; end;
How to automate the check? • Yes – Metadata • Capture row and columns information: • SASHELP.VTABLE select nobs, nvar into :rT1, :cT1 from sashelp.vtable where libname="WORK" and memname=upcase("T1") %let a_sizeT1=%eval(%eval(&rT1.)*(%eval(&cT1.-1)));;
How to automate the check?(cont.) • Automate array creation step for # of tables in set • Automate within check to run through for more than 1 table • Automate between checks to run for more than one set
Christine Davies Research Statistician 1 919.267.3747 cdavies@rti.org More Information