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

Reply
Contributor
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
1112017-03-012017-03-30A
1112017-03-012017-03-30B
1112017-03-152017-04-15C_D
1122017-08-102017-09-10A
1122017-08-102017-09-10A_B

 

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;
output;
end;
run;

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

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;
quit;

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
  • 113 views
  • 0 likes
  • 2 in conversation