It's a bit difficult to answer your request because you provide a table of sample data, but after reviewing your code, it's clear that the actual source data looks much different. I understand the actual data is likely to be sensitive, and contain a bunch of variables outside the scope of the question, but providing a simplified version of this data would make it easier to provide good ansewrs. I took a bit of a stab at recreating what I'm guessing your underlying data looks like. I figured theres probably 3 datasets: a dataset of cases (case_info), a dataset of people (people_info), and a dataset to contain the many-to-many relationship between those two datasets (case_to_people). I discovered this cool %age macro on the SAS site -- perhaps that is what you are using. So with the above data and the macro, the solution is quite simple -- it can be done in a single PROC SQL quite easily. It could also be done with one or more datasteps without much trouble as well, especially if the data is small enough be quickly joined with a couple of sort/sort/merges.For small amounts of data, I think that things like index lookups and hash tables are more trouble than they are worth. So take a look at this and see if this helps. %macro age(date,birth); %* this macro obtained from "Calculating Age with Only One Line of Code" by William Kreuter *; %* http://support.sas.com/kb/24/808.html *; floor ((intck('month',&birth,&date) - (day(&date) < day(&birth))) / 12) %mend age; data case_info; input @1 case_no $8. @11 dtdue date9.; format dtdue date9.; datalines; S6034840 05mar2012 S5439846 15mar2012 S5324014 20mar2012 ; run; data case_to_people; input @1 case_no $8. @11 id 5.; datalines; S6034840 00001 S6034840 00006 S5439846 00002 S5324014 00003 S5324014 00004 S5324014 00005 ; run; data people_info; input @1 id 5. @8 DOB date9.; format DOB date9.; datalines; 00001 15jun1989 00002 13jun1997 00003 17jun1988 00004 21jun1990 00005 22jun1993 00006 19jun1988 ; run; * This dataset is for purposes of human-review of the data / verifying the solution. *; proc sql; create table cases_and_ages as select a.*, c.*, %age (a.dtdue, c.DOB) as age from case_info a inner join case_to_people b on a.case_no = b.case_no inner join people_info c on b.id = c.id order by a.case_no, c.DOB ; quit; * This is the actual solution *; proc sql; create table cases_with_under_21 as select distinct a.case_no from case_info a inner join case_to_people b on a.case_no = b.case_no inner join people_info c on b.id = c.id where %age (a.dtdue, c.DOB) < 21 order by a.case_no ; quit;
... View more