topic Re: Data Selection in SAS Programming

Data Selection

thb — Mon, 08 May 2017 17:13:56 GMT

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

Re: Data Selection

ballardw — Mon, 08 May 2017 17:20:16 GMT

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?

Re: Data Selection

thb — Mon, 08 May 2017 17:22:16 GMT

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.

Re: Data Selection

mkeintz — Mon, 08 May 2017 17:37:07 GMT

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;

Re: Data Selection

art297 — Mon, 08 May 2017 17:33:44 GMT

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

Re: Data Selection

Ksharp — Tue, 09 May 2017 13:29:55 GMT

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;