topic Re: identify IDs who have received both medications in SAS Data Management

identify IDs who have received both medications

GreenTree1 — Thu, 21 May 2020 13:59:05 GMT

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

Re: identify IDs who have received both medications

novinosrin — Thu, 21 May 2020 14:08:46 GMT

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;

Re: identify IDs who have received both medications

GreenTree1 — Thu, 21 May 2020 14:17:22 GMT

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

Re: identify IDs who have received both medications

GreenTree1 — Thu, 21 May 2020 14:18:05 GMT

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

Re: identify IDs who have received both medications

novinosrin — Thu, 21 May 2020 14:19:38 GMT

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;

Re: identify IDs who have received both medications

GreenTree1 — Thu, 21 May 2020 14:33:31 GMT

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.

Re: identify IDs who have received both medications

novinosrin — Thu, 21 May 2020 14:38:25 GMT

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

Re: identify IDs who have received both medications

GreenTree1 — Thu, 21 May 2020 15:04:18 GMT

okay 🙂 , yes trying it now

Re: identify IDs who have received both medications

ballardw — Thu, 21 May 2020 15:22:24 GMT

@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-data-AKA-generate/ta-p/258712 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.

Re: identify IDs who have received both medications

GreenTree1 — Fri, 22 May 2020 13:13:27 GMT

yayyy the first one worked. Thanks novin!

Re: identify IDs who have received both medications

GreenTree1 — Fri, 22 May 2020 13:15:17 GMT

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