My data currently has multiple rows per person, and each of these rows contain an array that looks at whether or not the person was on a drug from 1-365. There are three different array's per person(A,B,C) since there are three classes of drugs that I am looking at.
I want to be able to see when one person is taking all three of them at the same time, and get a count of how many times it happends for the entire year.
example: IF A[112]='1' and B[112]= '1' and C[112]='1' then overlap_count=(1)
since these are all on seperate rows I would need the count to work on a key level and have the overlap_count displayed on each of the rows.
Ok, given the additional complexity, I tend to agree with @LinusH.
However, having followed the DOW loop approach so far, it is fun to adapt it to the new specifications!
So, let's first create new test data:
data have;
length person 8
drug $1;
array ondrug[365] $1;
do person=1 to 10;
nd=int(25*ranuni(2718))+1;
do i=1 to nd;
drug=byte(65+int(3*ranuni(2718)));
do _n_=1 to 365;
ondrug[_n_]=put(int(1.05*ranuni(2718)), 1.);
end;
output;
end;
end;
drop i nd;
run;
proc print data=have;
var person drug ondrug1-ondrug8;
run;
Does this look more familiar?
If so, the following, not too difficult modifications to the earlier program should do the trick:
data want;
do until(last.person);
set have;
by person;
array ondrug[365] $1;
array ndrugs[365] $3;
do d=1 to 365;
if drug='A' & ondrug[d]='1' then substr(ndrugs[d],1,1)='1';
else if drug='B' & ondrug[d]='1' then substr(ndrugs[d],2,1)='1';
else if drug='C' & ondrug[d]='1' then substr(ndrugs[d],3,1)='1';
end;
end;
do d=1 to 365;
overlap_count=sum(overlap_count, ndrugs[d]='111');
end;
do until(last.person);
set have;
by person;
output;
end;
drop d ndrugs:;
run;
Here's a form you can use:
data want;
do until (last.person);
set have;
by person;
*** calculate whatever it is you would like to calculate, making sure that the final values are the ones to be appended to each record;
end;
do until (last.person);
set have;
by person;
output;
end;
run;
If you're looking for more examples in the literature of published papers, it's commonly called a DOW loop.
Good luck.
I assume that your dataset has only one array containing 365 0-1-indicators (length $1) and that different rows of one person correspond to different classes of drugs. Hence, there should be three rows per person.
Dummy data for 10 persons and drug classes 'A', 'B', 'C' could be created as follows:
data have;
length person 8
drug $1;
array ondrug[365] $1;
do person=1 to 10;
do drug='A', 'B', 'C';
do _n_=1 to 365;
ondrug[_n_]=put(int(1.25*ranuni(2718)), 1.);
end;
output;
end;
end;
run;
proc print data=have;
var person drug ondrug1-ondrug8;
run;
Does this look familiar?
If so, you could build upon @Astounding's code template like this:
data want;
do until(last.person);
set have;
by person;
array ondrug[365] $1;
array ndrugs[365] 3;
do d=1 to 365;
ndrugs[d]=sum(ndrugs[d], input(ondrug[d], 1.));
end;
end;
do d=1 to 365;
overlap_count=sum(overlap_count, ndrugs[d]=3);
end;
do until(last.person);
set have;
by person;
output;
end;
drop d ndrugs:;
run;
There are indeed many interesting papers on the DOW loop:
"The DOW (not that DOW!!!) and the LOCF in Clinical Trials" should be a good starting point. Further examples from the pharmaceutical area can be found in the award-winning paper "Practical Uses of the DOW Loop in Pharmaceutical Programming." Technically more advanced is "The DOW-Loop Unrolled."
To avoid more or less complicated array and do-loop processing, transpose your data, so there are 365 rows per person, and one column for each drug.
Then you can use a simple SQL to query your data.
Ok, given the additional complexity, I tend to agree with @LinusH.
However, having followed the DOW loop approach so far, it is fun to adapt it to the new specifications!
So, let's first create new test data:
data have;
length person 8
drug $1;
array ondrug[365] $1;
do person=1 to 10;
nd=int(25*ranuni(2718))+1;
do i=1 to nd;
drug=byte(65+int(3*ranuni(2718)));
do _n_=1 to 365;
ondrug[_n_]=put(int(1.05*ranuni(2718)), 1.);
end;
output;
end;
end;
drop i nd;
run;
proc print data=have;
var person drug ondrug1-ondrug8;
run;
Does this look more familiar?
If so, the following, not too difficult modifications to the earlier program should do the trick:
data want;
do until(last.person);
set have;
by person;
array ondrug[365] $1;
array ndrugs[365] $3;
do d=1 to 365;
if drug='A' & ondrug[d]='1' then substr(ndrugs[d],1,1)='1';
else if drug='B' & ondrug[d]='1' then substr(ndrugs[d],2,1)='1';
else if drug='C' & ondrug[d]='1' then substr(ndrugs[d],3,1)='1';
end;
end;
do d=1 to 365;
overlap_count=sum(overlap_count, ndrugs[d]='111');
end;
do until(last.person);
set have;
by person;
output;
end;
drop d ndrugs:;
run;
Corresponing SQL would be something like
select person, count(*) as no
from have_transposed
where sum(drug1, drug2, drug3) = 3
group by person
;
Not saying it's better, but...
😉
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.