BookmarkSubscribeRSS Feed
Obsidian | Level 7

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!

Diamond | Level 26 RW9
Diamond | Level 26

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 2 in conversation