Tom's code is fairly easy to understand if you add in some of the extra variables that he didn't include in the file. e.g., if you run: proc sql noprint ; create table want as select a.id,count(distinct b.id) as numberofid ,a.type,b.id as b_id from have a left join have b on a.type=b.type and a.id ^= b.id group by a.id ; quit; you would get the following file: id numberofid type b_id A 3 K3 B A 3 K2 C A 3 K2 D A 3 K3 C A 3 K1 B 2 K3 C B 2 K3 A C 3 K2 A C 3 K3 B C 3 K2 D C 3 K3 A D 2 K2 A D 2 K2 C Tom used a left join so there MUST be at least one record for each ID/type combination in the original file. However, since he excluded matches where a.id = b.id, the one non-match record for A/type K1 doesn't get an assignment for b.id. All of the other a.id/type combination had matching b.ids, thus they appear in the b.id column. Then, since he is using the count function on distinct b.id/type values, the missing value and duplicates dont get counted.
... View more