topic Re: Conditionally match merging records from two datasets without a common variable? in SAS Programming

Conditionally match merging records from two datasets without a common variable?

Ian_K — Mon, 29 Jun 2015 18:32:18 GMT

I would like to match a maximum of 10 pharmaceuticals to each patient based on the symptoms addressed by the drugs and the symptoms recorded for each patient.

However, there is not a common variable to which to merge on and merging all pharmaceuticals to all patients and using the index function to find suitable drugs is not practical as it would result in a dataset with the number of records equivalent to 200 million drugs x 25 million patients (or 5,000 trillion records).

* THE DATASET PHARMACEUTICALS IN REALITY HAS 200 MILLION RECORDS;

DATA PHARMACEUTICALS;INPUT DRUG_ID SYMPTOM_WORD1 $ SYMPTOM_WORD2 $ SYMPTOM_WORD3 $;

CARDS;

1022 BP ARRYTHMIA

1023 ANGINA

1024 BP ANGINA PALP

;

RUN;

* THE DATASET PATIENTS IN REALITY HAS 25 MILLION RECORDS;

* SYMPTOM_WORDS ARE CONCATENATED TOGETHER AND SEPARTED BY COMMAS;

DATA PATIENTS;INPUT PATIENT_ID AILMENT_WORDS $;

CARDS;

245762 DIAHREA,NASEAU,ABDOMINAL CRAMPS

238761 MIGRAINE,ANGINA

239978 BP,HEADACHE,COUGH,THROAT PAIN

Ideally, if there was a common variable I would merge the two datasets and execute an INDEX function to find drugs that match patient symptoms and set a counter to limit these matches to 10 per patient, like this:

IF INDEX(AILMENT_WORDS, SEARCH_WORD1) GE 1 THEN EVENTMATCH+1;

IF INDEX(AILMENT_WORDS, SEARCH_WORD2) GE 1 THEN EVENTMATCH+1;

IF INDEX(AILMENT_WORDS, SEARCH_WORD3) GE 1 THEN EVENTMATCH+1;

I would set a loop counter using first.patient_ID to ensure no more than 10 drug matches per patient. However, as I cannot perform the merge “BY PATIENT_ID” as it is not common to both datasets I therefore cannot use first.patient_ID.

Any ideas?

Re: Conditionally match merging records from two datasets without a common variable?

ballardw — Mon, 29 Jun 2015 19:43:59 GMT

Do you want different lists of 10 drugs for each instance of BP or the same for all BP (and so on for each ailment)?

Re: Conditionally match merging records from two datasets without a common variable?

Ian_K — Mon, 29 Jun 2015 20:58:17 GMT

Hi Ballardw,

I'm just looking for 10 match drugs for any given ailment. Therefore, it is possible that some ailments will not be addressed for given patients.

So the end result would be something along the lines of:

User1 drug1

User1 drug2

user1 drug3

...

user1 drug10

I know that sounds strange, but it is a theoretical rather than a practical exercise.

Thank you for replying!

Re: Conditionally match merging records from two datasets without a common variable?

Ian_K — Tue, 30 Jun 2015 02:44:42 GMT

I'm looking for a list of a maximum of 10 drugs per patient. Accordingly some ailments will not be addressed, but all patients will be addressed.

Re: Conditionally match merging records from two datasets without a common variable?

KachiM — Tue, 30 Jun 2015 10:47:01 GMT

Ian,

You have ailment-words from a Patient and have symptom lists by drug_id. How would the OUTPUT look-like? Should there be the Drug_Id or Symptom_word for the matching Ailment_word? A simple OUTPUT data set with the related PHARMA and PATIENT data sets will easily explain what you want, instead of thousand words of explanation.

Re: Conditionally match merging records from two datasets without a common variable?

Ksharp — Tue, 30 Jun 2015 14:34:12 GMT

It sounds like you need Hash Table. Hope you have enough Memory to handle it .

Re: Conditionally match merging records from two datasets without a common variable?

PGStats — Tue, 30 Jun 2015 18:05:42 GMT

A conventional approach seems the most scalable:

DATA PHARMACEUTICALS;

infile datalines missover;

INPUT DRUG_ID (SYMPTOM_WORD1 SYMPTOM_WORD2 SYMPTOM_WORD3) (:$16.);

datalines;

1022 BP ARRYTHMIA

1023 ANGINA

1024 BP ANGINA PALP

;

data pharm;

set pharmaceuticals;

array symptom_{*} SYMPTOM_WORD:;

do i = 1 to dim(symptom_);

symptom = symptom_{i};

if not missing(symptom) then output;

end;

keep DRUG_ID symptom;

run;

DATA PATIENTS;

INPUT PATIENT_ID AILMENT_WORDS &:$40.;

datalines;

245762 DIAHREA,NASEAU,ABDOMINAL CRAMPS

238761 MIGRAINE,ANGINA

239978 BP,HEADACHE,COUGH,THROAT PAIN

;

data pat;

set PATIENTS;

length ailment $16;

do i = 1 to countw(AILMENT_WORDS, ",");

ailment = scan(AILMENT_WORDS, i, ",");

output;

end;

keep PATIENT_ID ailment;

run;

proc sql;

create table drugs as

select PATIENT_ID, DRUG_ID

from pat as a inner join

pharm as b on b.symptom = a.ailment

order by PATIENT_ID;

quit;

data drugs10;

set drugs; by PATIENT_ID;

if first.PATIENT_ID then count = 0;

count + 1;

if count <= 10;

drop count;

run;

Re: Conditionally match merging records from two datasets without a common variable?

Ian_K — Tue, 30 Jun 2015 18:27:03 GMT

Thank you PG Stats for your partial solution - much appreciated!!

Ian

Each step works fine until we get to the PROC SQLs. You can see below that at line 44 we end up with zero obs.

Here is the log file from the SQL steps:

39 proc sql;

40 create table drugs as

41 select PATIENT_ID, DRUG_ID

42 from pat as a inner join

43 pharm as b on b.symptom = a.ailment

44 order by PATIENT_ID;

NOTE: Table WORK.DRUGS created, with 0 rows and 2 columns.

45 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.31 seconds

cpu time 0.06 seconds

47 data drugs10;

48 set drugs; by PATIENT_ID;

49 if first.PATIENT_ID then count = 0;

50 count + 1;

51 if count <= 10;

52 drop count;

53 run;

NOTE: There were 0 observations read from the data set WORK.DRUGS.

NOTE: The data set WORK.DRUGS10 has 0 observations and 2 variables.

NOTE: DATA statement used (Total process time):

real time 0.03 seconds

cpu time 0.01 seconds

Re: Conditionally match merging records from two datasets without a common variable?

PGStats — Tue, 30 Jun 2015 19:42:16 GMT

When I run the code that I posted, I get 4 rows from the SQL step. Note that the join in the SQL step is case-sensitive, it requires exact matches between symptom and ailment words.

Re: Conditionally match merging records from two datasets without a common variable?

PGStats — Tue, 30 Jun 2015 19:49:28 GMT

Note also that it might be a good idea to add the DISTINCT option to the query for patients with many ailments related to the same drug :

proc sql;

create table drugs as

select DISTINCT PATIENT_ID, DRUG_ID

from pat as a inner join

pharm as b on b.symptom = a.ailment

order by PATIENT_ID;

quit;

Re: Conditionally match merging records from two datasets without a common variable?

Ian_K — Wed, 01 Jul 2015 18:45:43 GMT

Hello PG Stats!

You are amazing!! Thank you once again.

I entered your code "exactly as is" (I copy and pasted it into SAS) at part 6 of this conversation and I still get 0 obs.

Sorry to bother you, but do you have any further ideas?

All the best to you,

Ian

Re: Conditionally match merging records from two datasets without a common variable?

Ian_K — Thu, 02 Jul 2015 17:20:37 GMT

PG STATS had some good suggestions, but they didn't completely work out.

Anybody else have any great ideas to solve this one?

Thank you to all!

Ian

Re: Conditionally match merging records from two datasets without a common variable?

KachiM — Thu, 02 Jul 2015 17:31:34 GMT

Ian,

PGStats solution gives the following OUTPUT. Do you want a different output? Please tell what you now get and what you want. It will help others to help you.

PATIENT_

Obs ID DRUG_ID

1 238761 1023

2 238761 1024

3 239978 1022

4 239978 1024

Re: Conditionally match merging records from two datasets without a common variable?

Ian_K — Thu, 02 Jul 2015 17:37:54 GMT

Hello datasp,

My comment (#7 above shows my LOG) says it all. When I copy and paste the code as is and run it in SAS I end up with Zero OBS in the SQL steps. I'm not sure why.... the output you show is exactly what I need, but it just isn't happening for me.

Thank you for replying,

Ian

Re: Conditionally match merging records from two datasets without a common variable?

KachiM — Thu, 02 Jul 2015 18:06:21 GMT

Ian,

I suspect your reading the input data sets is not OK. Better use Proc Print to see the values. Proc SQL is OK.

Re: Conditionally match merging records from two datasets without a common variable?

Ian_K — Thu, 02 Jul 2015 19:15:53 GMT

Hello Datasp,

The proc print output for the pharm input data is this:

Obs DRUG_ID symptom

1 1022 BP

2 1022 ARRYTHMIA

3 1023 ANGINA

4 1024 BP

5 1024 ANGINA

6 1024 PALP

And for the Patient input data is this:

PATIENT_

Obs ID AILMENT_WORDS

1 245762 DIAHREA,NASEAU,ABDOMINAL CRAMPS

2 238761 MIGRAINE,ANGINA

3 239978 BP,HEADACHE,COUGH,THROAT PAIN

Re: Conditionally match merging records from two datasets without a common variable?

KachiM — Thu, 02 Jul 2015 20:50:38 GMT

Ian,

I don't know why?

IAN has to come to your rescue!

Re: Conditionally match merging records from two datasets without a common variable?

PGStats — Thu, 02 Jul 2015 22:01:14 GMT

Very puzzling. Start a new SAS session, run the code, and post the whole log. - PG

Re: Conditionally match merging records from two datasets without a common variable?

Ian_K — Sat, 04 Jul 2015 18:14:24 GMT

I think that PG Stat has provided a completely correct solution.

I suspect that I am having a problem that I will refer to my IT group.

Thank you,

Ian