Sample 29177: Verifying Data
 |  |  |  |  |  |
Two data sets are created. The first is a master data set of department ids, positions, and employee ids. Two indices are created on it: department/position and position/employee.
The second data set is one derived from the master with some of the variable values changed and will checked against the master for bad records. There is a chance that no bad observations are created.
The second is checked against the first using the KEY= option of the SET statement. First, the department/position combinations are checked. If an invalid combination is found the record is written out to a data set.
Next, the position/employee id combination is checked and written out if not found.
About the Author
Richard Wright began writing SAS code over 25 years ago keying code and data on punch cards as a student at University of Oregon. Since then he has worked at various agencies for the state of Texas. He is certified in Base and Advanced SAS.
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
DATA lookup(INDEX=(ind1=(deptid position_nbr) ind2=(position_nbr emplid)));
/*
* data set of department id, position number and employee id
* create two indices on: deptid-position_nbr and position_nbr-emplid
*/
LENGTH deptid position_nbr $ 8 emplid $ 11;
INPUT deptid position_nbr emplid $;
CARDS;
00251972 00059929 00000000569
00321998 00027661 00000001185
00643431 00027713 00000002317
00451323 00026160 00000002609
00241712 00028335 00000004338
00671712 00028335 00000004338
00451045 00024908 00000008507
00673736 00028176 00000008647
00721200 00024077 00000009968
00441309 00027114 00000012192
;;;;
DATA dummy;
/*
* create some dummy data to test
* about 1/4 of the good data will be changed
* of those, change 1/3 of deptid, 1/3 of the position_nbr, 1/3 of the employee id
*/
SET lookup;
IF Uniform(0) GE .75 THEN DO; /* change about 1 quarter of the real data */
s = Uniform(0); /* select which variable to change */
SELECT;
WHEN ( 0 <= s <= .333) Substr(deptid, Ceil(8*Uniform(0)),1) = Put(Ceil(10*Uniform(0)),$1.);
WHEN ( .333 < s <= .667) Substr(position_nbr, Ceil(8*Uniform(0)),1) = Put(Ceil(10*Uniform(0)),$1.);
OTHERWISE Substr(emplid, Ceil(11*Uniform(0)),1) = Put(Ceil(10*Uniform(0)),$1.);
END;
END;
DROP s;
RUN;
DATA bad(KEEP=deptid position_nbr emplid whats_wrong);
LENGTH whats_wrong $ 50;
SET dummy; /* bring in the doctored data */
SET lookup KEY=ind1 /UNIQUE; /* see if deptid/position is good */
IF _iorc_=%sysrc(_DSENOM) THEN DO; /* something wrong with this combination */
whats_wrong = "No Match on deptid/position combination";
_ERROR_ = 0; /* reset the error flag */
OUTPUT; /* kick out the observation */
RETURN; /* go for next record */
END;
SET lookup KEY=ind2 /UNIQUE; /* see if position/emplid comb is good */
IF _iorc_=%sysrc(_DSENOM) THEN DO; /* no, so repeat the above process */
_ERROR_ = 0;
whats_wrong = "No Match on position/emplid combination";
OUTPUT;
RETURN;
END;
RUN;
PROC PRINT DATA=lookup;
PROC PRINT DATA=dummy;
PROC PRINT DATA=bad; /* there may be no bad records */
RUN;
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
The SAS System 5
08:34 Thursday, July 5, 2007
position_
Obs deptid nbr emplid
1 00251972 00059929 00000000569
2 00321998 00027661 00000001185
3 00643431 00027713 00000002317
4 00451323 00026160 00000002609
5 00241712 00028335 00000004338
6 00671712 00028335 00000004338
7 00451045 00024908 00000008507
8 00673736 00028176 00000008647
9 00721200 00024077 00000009968
10 00441309 00027114 00000012192
The SAS System 6
08:34 Thursday, July 5, 2007
position_
Obs deptid nbr emplid
1 00257972 00059929 00000000569
2 00321998 00027661 00000001185
3 00643431 00327713 00000002317
4 00457323 00026160 00000002609
5 00241712 00028335 00000004338
6 00671712 00028335 00000004338
7 00451045 00024908 00000008507
8 00673736 00028176 00000008647
9 00721200 00024077 00000009968
10 00441309 00027114 00000012192
The SAS System 7
08:34 Thursday, July 5, 2007
position_
Obs deptid nbr emplid
1 00257972 00059929 00000000569
2 00643431 00327713 00000002317
3 00457323 00026160 00000002609
Output
position_
Obs deptid nbr emplid
1 00251972 00059929 00000000569
2 00321998 00027661 00000001185
3 00643431 00027713 00000002317
4 00451323 00026160 00000002609
5 00241712 00028335 00000004338
6 00671712 00028335 00000004338
7 00451045 00024908 00000008507
8 00673736 00028176 00000008647
9 00721200 00024077 00000009968
10 00441309 00027114 00000012192
position_
Obs deptid nbr emplid
1 00251972 00059929 00000000569
2 00321998 00027661 00000001185
3 00643431 00027713 00000002317
4 00451323 00026160 00000002609
5 00241712 00028335 00000004338
6 00671712 00028335 00000004338
7 00481045 00024908 00000008507
8 00673736 00028176 00000008647
9 00721200 00034077 00000009968
10 00441309 00027114 00000012192
position_
Obs whats_wrong deptid nbr emplid
1 No Match on deptid/position combination 00481045 00024908 00000008507
2 No Match on deptid/position combination 00721200 00034077 00000009968
This sample uses the KEY= option to validate variable values against composite keys.
| Type: | Sample |
| Date Modified: | 2008-02-28 08:04:19 |
| Date Created: | 2007-08-03 03:02:57 |
Operating System and Release Information
| Product Family | Product | Host | SAS Release | |
| Starting | Ending | |||
| SAS System | Base SAS | All | n/a | n/a |