BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cmahrb2
Calcite | Level 5

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.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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! Smiley Happy

 

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;

View solution in original post

7 REPLIES 7
Reeza
Super User
Look at the RETAIN function. Beyond that I think you'll need to post sample data and output that you want for more help.
Astounding
PROC Star

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.

FreelanceReinh
Jade | Level 19

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

LinusH
Tourmaline | Level 20

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
cmahrb2
Calcite | Level 5
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.
FreelanceReinh
Jade | Level 19

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! Smiley Happy

 

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;
LinusH
Tourmaline | Level 20

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1431 views
  • 2 likes
  • 5 in conversation