@telc24 wrote:
@mkeintz Apologies, I should have specified more clearly- the 'rules' for my indicator variable are in my "brute force" method. I've pasted them again below.
data student2; set student;
if county=1 and (school1=1 or school4=1) then indicator=1;
if county in (2 3) and (school2=1) then indicator=2;
if county=4 and (school3=1 or school5=1 or school6=1) then indicator=3;
if county=5 and (school7=1) then indicator=4;
run;
OK, I think I get it. You want an indicator for each unique set of schools found in the county dataset. You are apparently assuming that
- A county can be served by a unique set of schools, none of which serve other schools.
or
- Multiple counties may share a unique set of schools.
- counties may not partially share schools (i.e. county A can't be served by schools 1,2, and 3 while county B is served by 1 and 2.
The program below should do:
data student;
input id $ school1 school2 school3 school4 school5 school6 school7 county;
cards;
A 0 0 0 1 0 0 0 2
B 1 1 0 0 0 0 0 1
C 1 0 1 0 0 1 0 3
D 0 1 0 0 0 0 0 2
E 0 0 0 0 1 1 0 4
F 0 0 0 0 0 0 1 5
;
run;
data county;
input county school1 school2 school3 school4 school5 school6 school7;
cards;
1 1 0 0 1 0 0 0
2 0 1 0 0 0 0 0
3 0 1 0 0 0 0 0
4 0 0 1 0 1 1 0
5 0 0 0 0 0 0 1
;
data want (keep=id indicator);
/* Use a hash object to detect and map each unique pattern of schools */
/* dummies found in the COUNTY data set to a unique INDICATOR value */
if _n_=1 then do c=1 by 1 until (end_of_counties);
set county end=end_of_counties;
length ones_and_zeroes $50;
ones_and_zeroes=cats(of sch:);
if c=1 then do;
declare hash patterns();
patterns.definekey('ones_and_zeroes');
patterns.definedata('indicator');
patterns.definedone();
end;
/* New pattern of zeros and ones? Then add to hash */
if patterns.find()^=0 then do;
indicator=patterns.num_items+1;
patterns.add();
end;
/* Map county to indicator (a proxy for school patterns) */
array cty_ind {5} _temporary_;
cty_ind{county}=indicator;
/* Map each school to indicator also */
array sch_ind {7} _temporary_;
array _sch {7} school1-school7;
do s=1 to dim(_sch);
if _sch{s}=1 then sch_ind{s}=indicator;
end;
end;
/** Now read student counties and compare to the map above **/
set student;
indicator=0;
/* Scan over all attended schools until a non-zero indicator */
/* is found, or no more schools to check */
schoolid=whichn(1,of school1-school7) ;
do while (schoolid^=0 and indicator=0);
if cty_ind{county}=sch_ind{schoolid} then indicator=sch_ind{schoolid};
if indicator=0 then _sch{schoolid}=0;
schoolid=whichn(1,of school1-school7) ; /*Go to next attended school */
end;
run;
The hash object is just used to record all the unique school combinations found in COUNTIES, incrementing INDICATOR each time a new combination is encountered. This also provides a map of COUNTIES to the indicator, as well as a map of its constituent schools to the indicator.
Then read the students. If any the student's schools maps to the same indicator as the student's county does, then you have a non-zero indicator value.
Additional explanatory notes:
The “trick” here is to realize that distinct sets of schools serve distinct counties. So instead of determining whether a student attends any school that serves the same county as the student lives in, you actually want to know if the student attends a school that is a member of a set-of-schools that serves that student's county.
Of course, this works because you assume that each school belongs to only one distinct set-of-schools
process the county dataset first.
First you have to set up indicator variables corresponding to these sets-of-schools. As you discover a new set, assign INDICATOR=1, then 2, etc. You do this by reading through the county dataset. With each incoming record, generate the “pattern” of schools serving that county (i.e. concatenated ones and zeroes representing the school dummies). So if school 1 and 4 serves the incoming county (school1=1 and school4=1), and all the other school vars are zero, then the pattern of zeroes-and-ones is 1001000.
Once the pattern is in hand, see if it has already been encountered via hash find method, which will retrieve the corresponding INDICATOR. But if it is new (find method failed), then set the new INDICATOR value to 1 more than the number of known patterns. Store that value in the hash object with a lookup key of 1001000 (or whatever the new key is). Effectively INDICATOR is nothing more than a id variable for each distinct pattern of schools in the COUNTY dataset, in the order they are encountered.
So much for patterns as lookup for INDICATOR. Now
- Put INDICATOR in the corresponding element of the county array CTY_IND.
- And then loop over the school dummies in the county dataset. For any dummy=1 put INDICATOR in the corresponding school array (SCH_IND) element.
So much for the county dataset, now for the students:
Finally you’re ready to process the students dataset. For each incoming student, get the position of the leftmost school with dummy=1 (that's the whichn(1,of school1-school7) statement). If that school is a member of the same set of schools as serve the student’s county-of-residence, then the two array elements (sch_ind{schoolid) and cnt_ind{county}) will have the same indicator value, and you don't need to examine any more schools for that student. But if not, then set that school dummy to zero, so the next left-most can be found and tested.
