Solved
New Contributor
Posts: 4

Counting across multiple Rows

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.

Accepted Solutions
Solution
‎11-24-2015 09:03 AM
Posts: 1,245

Re: Counting across multiple 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&colon;

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

All Replies
Super User
Posts: 23,681

Re: Counting across multiple Rows

Look at the RETAIN function. Beyond that I think you'll need to post sample data and output that you want for more help.
Super User
Posts: 6,753

Re: Counting across multiple Rows

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.

Posts: 1,245

Re: Counting across multiple Rows

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."

Super User
Posts: 5,876

Re: Counting across multiple Rows

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.

Data never sleeps
New Contributor
Posts: 4

Re: Counting across multiple Rows

Each time a person has a new drug, they are on a new line. So a person could have 10 of drug A, two of drug B and 1 of drug C for a total of 13 lines for this person. So there is the challenge. Each line has a indicator of which drug it is, but it is more complex than just one array per person.
Solution
‎11-24-2015 09:03 AM
Posts: 1,245

Re: Counting across multiple 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&colon;

``````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;
``````
Super User
Posts: 5,876

Re: Counting across multiple Rows

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...

;-)

Data never sleeps
🔒 This topic is solved and locked.