identify IDs who have received both medications

[GreenTree1]

Hello Everyone,

I have a dataset for multiple records/ patient. Each record indicates a separate clinic visit for a certain patient identified by a unique id.

patient.     unique id

patient 1-  abcd

patient 2-  efgh

patient 3.   ijkl

the data looks as follows 

unique id   clinic visit.  Aspirin   statin

abcd           1                 1

abcd           2                 1

abcd.          3

abcd           4                                1

efgh            1                                1

efgh.           2

efgh.          3                                 1

ijkl              1                1.              1

ijkl              2.                

ijkl              3                1

ijkl.             4                                

So looking at this, patients were either prescribed aspirin and statin on the same day (patient ijkl) or were only prescribe statin (patient efgh) or were prescribed both aspirin and statin but at different visits (patient abcd). My goal is to identify patients who have been prescribed aspirin and statin regardless of the visit. For clarification, I would like to identify patients abcd and ijkl in this instance. My understanding is that if I use the "if aspirin=1 AND statin=1 then var1=1..." command it will look across each record individually and not consider that those records belong to a single patient.

Happy to provide more details

appreciate your help

[novinosrin · Accepted solution]

Hi @GreenTree1  Can you please provide a well formatted sample data that is usable to copy/paste and test. Well, here is a untested solution 

proc sql;
create table want as
select *
from have
group by unique_id
having sum(max(Aspirin),max(statin))>=2
order by unique_id,clinic_visit;
quit;

[GreenTree1]

Hi Novin,
Thank you, will an excel sheet work?

[novinosrin]

I assumed your source is a SAS dataset. Is it excel? If yes, have you imported your excel into SAS yet?

Once that is done, you could also consider the following boring predictable solution in my opinion

data want;
 do _n_=1 by 1 until(last.unique_id);
  set have;
  by unique_id;
  if n(statin) then _ns=statin;
  if n(aspirin) then _na=aspirin;
 end;
 _s=sum(_ns,_na);
 do _n_=1 to _n_;
  set have;
  if _s>=2 then output;
 end;
 drop _:;
run;

[GreenTree1]

Hi novin, it is a sas dataset, I was just suggesting an excel format as I thought that my posted question was not clear enough.

[ballardw]

---

@GreenTree1 wrote:
Hi Novin,
Thank you, will an excel sheet work?

---

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against. The code box is important as the message windows on this forum will reformat plain text and the code often will not run properly when copied from the forum to a SAS session editor and executed.

[GreenTree1]

Thanks Ballard,
Never tried this, I will give this a shot instead of typing it out

[GreenTree1]

I can create an excel sheet of the data provided but wont be able to share the real data

[novinosrin]

Ah okay, I was only requesting a neat mock sample. lol. Nevermind. Have  you tested the code I provided with your real dataset yet?

[GreenTree1]

okay 🙂 , yes trying it now

[GreenTree1]

yayyy the first one worked. Thanks novin!