topic Re: Counting across multiple Rows in ODS and Base Reporting

Counting across multiple Rows

cmahrb2 — Thu, 19 Nov 2015 20:00:31 GMT

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.

Re: Counting across multiple Rows

Reeza — Thu, 19 Nov 2015 20:07:50 GMT

Look at the RETAIN function. Beyond that I think you'll need to post sample data and output that you want for more help.

Re: Counting across multiple Rows

Astounding — Thu, 19 Nov 2015 20:44:33 GMT

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.

Re: Counting across multiple Rows

FreelanceReinh — Thu, 19 Nov 2015 22:39:23 GMT

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

Re: Counting across multiple Rows

LinusH — Fri, 20 Nov 2015 13:04:12 GMT

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.

Re: Counting across multiple Rows

cmahrb2 — Fri, 20 Nov 2015 13:27:22 GMT

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.

Re: Counting across multiple Rows

FreelanceReinh — Fri, 20 Nov 2015 14:26:48 GMT

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;

Re: Counting across multiple Rows

LinusH — Tue, 24 Nov 2015 11:36:19 GMT

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

😉