topic Re: Filtering Health Data using Drug Identification Numbers (DINs) in SAS Programming

Filtering Health Data using Drug Identification Numbers (DINs)

Asboaheng — Wed, 21 Oct 2020 23:42:35 GMT

Hello, I am trying to filter a health data to identify my study population using specific Drug Identification Numbers (DINs) for asthma inhaler medications. I have a list of DINs I am interested in. I will like SAS to identify individuals and observations that matches one of the DINs. Here are some of the DINs I am interested in: 812463; 824216, 851752;851760;851841 etc. I really need help on this. Any comments would be greatly appreciated.

Thank you

Re: Filtering Health Data using Drug Identification Numbers (DINs)

Kurt_Bremser — Wed, 21 Oct 2020 23:56:33 GMT

Simple with SQL, if dataset size is not that large:

proc sql;
create table want as
  select *
  from have
  where din in (select din from reference)
;
quit;

Re: Filtering Health Data using Drug Identification Numbers (DINs)

ChrisNZ — Thu, 22 Oct 2020 00:15:12 GMT

I think this syntax is better not shown; it's disastrous when the volumes grow.

An inner join is always valid:

proc sql; create table WANT as select HAVE.* from HAVE, DINLIST where HAVE.DIN = DINLIST.DIN; quit;

Re: Filtering Health Data using Drug Identification Numbers (DINs)

ChrisNZ — Thu, 22 Oct 2020 00:16:34 GMT

Or if there are duplicates:

proc sql; create table WANT as select HAVE.* from HAVE, (select unique DIN from DINLIST) DINLIST where HAVE.DIN = DINLIST.DIN; quit;

Re: Filtering Health Data using Drug Identification Numbers (DINs)

ballardw — Thu, 22 Oct 2020 00:53:12 GMT

Another option is to create a custom format that has a formatted value of "Want", or similar, for the values of interest and "Not wanted" for other values.

Then you can either select records with a where statement or use the formatted value directly, depending on need.

Re: Filtering Health Data using Drug Identification Numbers (DINs)

Reeza — Thu, 22 Oct 2020 01:34:40 GMT

1. Use a WHERE statement, applied in either SQL or Data step

where DIN in (812463, 824216.....);

2. Create a lookup data set

data dins_asthma;
input DIN;
cards;
812463
824216
851752
;;;;
run;

Then any of the other suggestions will work for you.

@Asboaheng wrote:

Hello, I am trying to filter a health data to identify my study population using specific Drug Identification Numbers (DINs) for asthma inhaler medications. I have a list of DINs I am interested in. I will like SAS to identify individuals and observations that matches one of the DINs. Here are some of the DINs I am interested in: 812463; 824216, 851752;851760;851841 etc. I really need help on this. Any comments would be greatly appreciated.

Thank you

Re: Filtering Health Data using Drug Identification Numbers (DINs)

andreas_lds — Thu, 22 Oct 2020 04:57:15 GMT

Maybe something solving your problem has already been suggested, if not you should post an excerpt of the data you have or the result of proc contents, so that we actually know how what "health data" is in your case.

Re: Filtering Health Data using Drug Identification Numbers (DINs)

Asboaheng — Thu, 22 Oct 2020 13:01:30 GMT

The data size is very large but i will try this whether it will work. Thank you.

Re: Filtering Health Data using Drug Identification Numbers (DINs)

Asboaheng — Thu, 22 Oct 2020 13:03:38 GMT

Thank you. Will try this option too. Just that i have a tall list of DIN lists (about 450 DINS).

Re: Filtering Health Data using Drug Identification Numbers (DINs)

Kurt_Bremser — Thu, 22 Oct 2020 13:07:48 GMT

Define "large".

The (probably) most efficient method would be a hash object in a data step, as long as you can fit the reference numbers into memory (likely, because you'll basically need 8 bytes * number of items, not much in the times of gigabyte RAM).

data want;
set have;
if _n_ = 1
then do;
  declare hash r (dataset:"reference");
  r.definekey("din");
  r.definedone();
end;
if r.check() = 0;
run;

The only sorting is done in memory on the lookup table.

Re: Filtering Health Data using Drug Identification Numbers (DINs)

ChrisNZ — Sat, 24 Oct 2020 09:19:43 GMT

@Kurt_Bremser Each item uses 48 bytes in a one-numeric-key hash table.

Re: Filtering Health Data using Drug Identification Numbers (DINs)

Kurt_Bremser — Sat, 24 Oct 2020 10:53:59 GMT

So we have 40 bytes overhead per item? That would be an interesting fact for future calculations of RAM requirements.

Re: Filtering Health Data using Drug Identification Numbers (DINs)

ChrisNZ — Sun, 25 Oct 2020 02:20:41 GMT

48 bytes is the minimum size (on 64-bit systems), then 16-byte increments.

See the macro I posted here, it explains the allocation logic, and does the calculations for you.

Re: Filtering Health Data using Drug Identification Numbers (DINs)

Kurt_Bremser — Tue, 27 Oct 2020 08:59:49 GMT

So the minimum overhead for the internal search table seems to be 32 bytes per item. The macro will come in handy, thanks for posting.

Re: Filtering Health Data using Drug Identification Numbers (DINs)

PeterClemmensen — Tue, 27 Oct 2020 09:45:02 GMT

For completenes.. If the range of DINs is somewhat sparse, this should be quite fast as well...

data din;
input din;
datalines;
812463
824216
851752
851760
851841
;

data have;
input din v;
datalines;
812463 1
123456 2
824216 3
234567 4
851752 5
345678 6
851760 7
456789 8
851841 9
;

data want;
   array d {0 : 999999} _temporary_;
   do until (z1);
      set din end = z1;
      d[din] = 1;
   end;
   do until (z2);
      set have end = z2;
      if d[din] then output;
   end;
run;