SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Counting drug use overlap along with the type of combination

Posts: 22

Counting drug use overlap along with the type of combination

I have a dataset that looks like the following. 

id from_dateto_datedrug


I want to count the number of days for which one or more drugs are used. I use the following codes:

data expand;
set have;
do day=from_date to to_date;

proc sql;
create table counted as
select *, count(distinct drug) as num_drug
from expand
group by id, day;

The problem with this code is that I cannot know which combination people are in and also sometimes undercounts and overcounts the number of drugs used. In the above example, I would want the combination of A, B and C_D to be counted as four (C_D is a combination product which contains two drugs), but I get the count as three. To account for this I created another dataset using the following code:


proc sql;
create table new_counted as
select *, case when drug in ("A_B", "C_D") then num_drug+1
else num_drug end as new_count
from counted
group by id, day,new_count desc;

This solves one problem but creates another. In the above example for id 112, it would give me a count of 3 which would be wrong (A and A_B should equal to two drug use). I am running in circles here. How could I get both number of days for total drugs use along with the type of combination? Thank you!

Super User
Super User
Posts: 9,441

Re: Counting drug use overlap along with the type of combination

Post test data in the form of a datastep, I am not here to type in test data or guess formats.  I would say the steps are:

1) Expand your current data creating a row for each drug, i.e. c_d would be split into two rows.

2) Sort by drug

3) datastep retain a count of days with a by group of drug

Ask a Question
Discussion stats
  • 1 reply
  • 2 in conversation