Data Selection

[thb]

Hello,

I have a dataset where I need to select a record if a record has data within all three metrics.  For example, 

Record_ID	Metric	Numerator	Denominator
1	Insulin	1	1000
1	Anti-Coagulants	3	1000
2	Insulin	0	3000
3	Insulin	1	2000
2	Anti-Coagulants	2	1000
4	Anti-Coagulants	1	4000
1	Opioids	1	1000
2	Opioids	1	2000
3	Insulin	2	1000

I only want to pull data that has a numerator for all three metrics (Insulin, Anti-Coagulants, and Opioids).  Although Record_ID 2 has a numerator in all three metrics, I do not want to pull it because it has a '0' for the Insulin metric.  Does anyone have a recommendation to selecting data this way?  The end table that I want would be:

Record_ID	Metric	Numerator	Denominator
1	Insulin	1	1000
1	Anti-Coagulants	3	1000
1	Opioids	1	1000

Any assistance would be greatly appreciated.

Thank you

[ballardw]

Will any of your Record_Id ever have more than 3 records? Fewer than 3 records? If so what is the selection rule(s)?

If they have exactly 3 records will they always be Insulin, Anti-coagulants and Opiods?

[thb]

Yes, they'll have multiple records by month from 2014 to present.  The criteria for selection is that they need to have records for these three metrics to be counted.  

[mkeintz]

If you never have more than one record for a given id/metric then sort by id and metric, then count metrics (with numberator>0) within each id:

Editted change:  (Looking at Art's solution revealed a flaw in my suggest - here's the revised response);

proc sort data=have (where=(numerator>0)) out=need;

  by id metric;

run;

data want;

  do n_metric=1 by 1 until (last.id);

    set need have (where=(numerator>0));

    by id;

  end;

  do until (last.id);

    set need have;

    by id;

    if n_metric=3 then output;

  end;

run;

[art297]

In that case I'd use a DOW loop to test whether each metric met the condition, e.g.:

proc sort data=have out=want;
  by Record_ID Metric;
run;

data want (drop=m1-m3);
  do until (last.Record_ID);
    set want;
    by Record_ID;
    if Metric eq 'Insulin' and Numerator gt 0 then m1=1;
    else if Metric eq 'Anti-Coagulants' and Numerator gt 0 then m2=1;
    else if Metric eq 'Opioids' and Numerator gt 0 then m3=1;
  end;
  do until (last.Record_ID);
    set want;
    by Record_ID;
    if sum(of m1-m3) eq 3 then output;
  end;
run;

Art, CEO, AnalystFinder.com

[Ksharp]

data have;
infile cards expandtabs truncover;
input Record_ID	Metric : $40.	Numerator	Denominator;
cards;
1	Insulin	1	1000
1	Anti-Coagulants	3	1000
2	Insulin	0	3000
3	Insulin	1	2000
2	Anti-Coagulants	2	1000
4	Anti-Coagulants	1	4000
1	Opioids	1	1000
2	Opioids	1	2000
3	Insulin	2	1000
;
run;
proc sql;
select *
 from have
   group by Record_ID
    having count(distinct Metric)=3 and sum(Numerator=0)=0;
quit;