BookmarkSubscribeRSS Feed
thb
Fluorite | Level 6 thb
Fluorite | Level 6

Hello,

 

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

 

Record_IDMetricNumeratorDenominator
1Insulin11000
1Anti-Coagulants31000
2Insulin03000
3Insulin12000
2Anti-Coagulants21000
4Anti-Coagulants14000
1Opioids11000
2Opioids12000
3Insulin21000

 

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_IDMetricNumeratorDenominator
1Insulin11000
1Anti-Coagulants31000
1Opioids11000

 

Any assistance would be greatly appreciated.

 

Thank you

 

 

5 REPLIES 5
ballardw
Super User

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
Fluorite | Level 6 thb
Fluorite | Level 6

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
PROC Star

 

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;

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
art297
Opal | Level 21

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
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 869 views
  • 0 likes
  • 5 in conversation