Hi all, I am working on the left data set (code used to create this is attached to bottom). My objective is to pick one MG, one inc and one CIS only using the logic below: - I want the to pick the CIS with the highest inc (income) within MG - If CIS is missing, then pick the first record with a non-missing CIS in the MG - else if CIS is present, pick the first record which has a CIS. If the logic is applied correctly , I should end up with a data set on the right. To accomplish this, I tried using the code below: proc sort data=nn; by MG Descending Inc CIS; run; data nnn1; set work.nn; by MG descending inc CIS; retain identifier; if first.MG then identifier=0; if identifier=0 and not missing(CIS) then do; output; identifier=1; end; run; But what I got back was this table Please can anyone help? Thanks. Code to create data: data nn; input MG Inc CIS; lines; 1 2345 . 1 45 12 1 23 12 2 33 23 2 500 . 2 300 . 2 123 2 3 . 10 3 129 10 2 350 . 4 . 23 4 330 23 5 190 23 5 456 . 6 838 . 6 345 . ;
... View more