topic Re: Scanning text and merging in SAS Programming

Scanning text and merging

newsas007 — Fri, 10 Jul 2020 04:22:11 GMT

Hi all,

This is my first time working with text data in SAS. I am working on scanning and merging by text data. The Dataset A contains a list of drugs and Dataset B has the actual drug. Each drug_list in data A is associated with a gene type. I want to merge the two datasets if the drug variable in Dataset B is listed in one of the drug list in Dataset A corresponding to the Gene type (G_type).

data A

ID	Date_a	G_type	Drug_list
1	1/2/2018	a	Afatinib,Cetuximab,Dacomitinib,Erlotinib,Gefitinib,Lapatinib,Necitumumab,Osimertinib,Panitumumab
1	2/10/2019	b	Cetuximab,Panitumumab
2	3/3/2019	c	Binimetinib,Cetuximab,Cobimetinib,Idelalisib,Panitumumab,Trametinib
3	1/2/2018	d	Alpelisib,Everolimus,Temsirolimus
4	2/10/2019	e	Bosutinib,Dasatinib,Everolimus,Temsirolimus
5	3/3/2019	a	Afatinib,Cetuximab,Dacomitinib,Erlotinib,Gefitinib,Lapatinib,Necitumumab,Osimertinib,Panitumumab
6	1/2/2018	c	Binimetinib,Cetuximab,Cobimetinib,Idelalisib,Panitumumab,Trametinib
7	2/10/2019	d	Alpelisib,Everolimus,Temsirolimus

Data B

ID	Drug	Date_b
1	Panitumumab	2/1/2018
1	Binimetinib	3/2/2019
1	Cetuximab	3/28/2019
2	Trametinib	3/23/2019
2	Temsirolimus	3/25/2019
3	Alpelisib	2/1/2018
5	Dacomitinib	3/21/2019
4	Everolimus	3/1/2019
6	Idelalisib	1/17/2018
7	Temsirolimus	3/2/2019

Data Want:

ID	Drug	Date_b	Date_a	G_type	Drug_List
1	Panitumumab	2/1/2018	1/2/2018	a	Afatinib,Cetuximab,Dacomitinib,Erlotinib,Gefitinib,Lapatinib,Necitumumab,Osimertinib,Panitumumab
1	Binimetinib	3/2/2019
1	Cetuximab	3/28/2019	2/10/2019	b	Cetuximab,Panitumumab
2	Trametinib	3/23/2019	3/3/2019	c	Binimetinib,Cetuximab,Cobimetinib,Idelalisib,Panitumumab,Trametinib
2	Temsirolimus	3/25/2019
3	Alpelisib	2/1/2018	1/2/2018	d	Alpelisib,Everolimus,Temsirolimus
5	Dacomitinib	3/21/2019	3/3/2019	a	Afatinib,Cetuximab,Dacomitinib,Erlotinib,Gefitinib,Lapatinib,Necitumumab,Osimertinib,Panitumumab
4	Everolimus	3/1/2019	2/10/2019	e	Bosutinib,Dasatinib,Everolimus,Temsirolimus
6	Idelalisib	1/17/2018	1/2/2018	c	Binimetinib,Cetuximab,Cobimetinib,Idelalisib,Panitumumab,Trametinib
7	Temsirolimus	3/2/2019	2/10/2019	d	Alpelisib,Everolimus,Temsirolimus

Re: Scanning text and merging

smantha — Fri, 10 Jul 2020 00:10:18 GMT

You can use a proc sql join
Proc sql;
Create table c as select a.*, b.* from a a, b b
Where index(b.drug, a.drug_list) >0;
Quit;

Re: Scanning text and merging

newsas007 — Fri, 10 Jul 2020 00:47:26 GMT

Here is the program and it doesn't work.

data A;
informat id 3. date_a mmddyy10. Drug_list $200.;
input id date_a Drug_list;
format id 3. date_a mmddyy10. Drug_list $200.;
cards;
1 1/2/2018 Afatinib,Cetuximab,Dacomitinib,Erlotinib,Gefitinib,Lapatinib,Necitumumab,Osimertinib,Panitumumab
1 2/10/2019 Cetuximab,Panitumumab
2 3/3/2019 Binimetinib,Cetuximab,Cobimetinib,Idelalisib,Panitumumab,Trametinib
3 1/2/2018 Alpelisib,Everolimus,Temsirolimus
4 2/10/2019 Bosutinib,Dasatinib,Everolimus,Temsirolimus
5 3/3/2019 Afatinib,Cetuximab,Dacomitinib,Erlotinib,Gefitinib,Lapatinib,Necitumumab,Osimertinib,Panitumumab
6 1/2/2018 Binimetinib,Cetuximab,Cobimetinib,Idelalisib,Panitumumab,Trametinib
7 2/10/2019 Alpelisib,Everolimus,Temsirolimus
;;;

data B;
informat id 3. date_b mmddyy10. Drug $20.;
input id date_b Drug;
format id 3. date_b mmddyy10. Drug $200.;
cards;
1 2/01/2018 Panitumumab
1 3/02/2019 Binimetinib
1 3/28/2019 Cetuximab
2 3/23/2019 Trametinib
2 3/25/2019 Temsirolimus
3 2/01/2018 Alpelisib
5 3/21/2019 Dacomitinib
4 3/01/2019 Everolimus
6 1/17/2018 Idelalisib
7 3/02/2019 Temsirolimus
;;;

Proc sql;
Create table c as select a.*, b.date_b, b.Drug from a a, b b
Where index(b.drug, a.drug_list) > 0;
Quit;

Here is the log:

137 Where index(b.drug, a.drug_list) > 0;
NOTE: The execution of this query involves performing one or more Cartesian product joins that can
not be optimized.
NOTE: Table WORK.C created, with 0 rows and 5 columns.

Re: Scanning text and merging

newsas007 — Fri, 10 Jul 2020 00:55:17 GMT

This doesn't work...

data have;

informat id 3. date_a mmddyy10. Drug_list $200.;

input id A Drug_list;

format id 3. A mmddyy10. Drug_list $200.;

cards;

1 1/2/2018 Afatinib,Cetuximab,Dacomitinib,Erlotinib,Gefitinib,Lapatinib,Necitumumab,Osimertinib,Panitumumab

1 2/10/2019 Cetuximab,Panitumumab

2 3/3/2019 Binimetinib,Cetuximab,Cobimetinib,Idelalisib,Panitumumab,Trametinib

3 1/2/2018 Alpelisib,Everolimus,Temsirolimus

4 2/10/2019 Bosutinib,Dasatinib,Everolimus,Temsirolimus

5 3/3/2019 Afatinib,Cetuximab,Dacomitinib,Erlotinib,Gefitinib,Lapatinib,Necitumumab,Osimertinib,Panitumumab

6 1/2/2018 Binimetinib,Cetuximab,Cobimetinib,Idelalisib,Panitumumab,Trametinib

7 2/10/2019 Alpelisib,Everolimus,Temsirolimus

;;;

data want;

informat id 3. date_b mmddyy10. Drug $20.;

input id A Drug;

format id 3. A mmddyy10. Drug $200.;

cards;

ID Date_b Drug

1 2/1/2018 Panitumumab

1 3/2/2019 Binimetinib

1 3/28/2019 Cetuximab

2 3/23/2019 Trametinib

2 3/25/2019 Temsirolimus

3 2/1/2018 Alpelisib

5 3/21/2019 Dacomitinib

4 3/1/2019 Everolimus

6 1/17/2018 Idelalisib

7 3/2/2019 Temsirolimus

;;;

Proc sql;
Create table c as select a.*, b.* from a a, b b
Where index(b.drug, a.drug_list) >0;
Quit;

Re: Scanning text and merging

smantha — Fri, 10 Jul 2020 02:41:41 GMT

data A;
informat id 3. date_a mmddyy10. Drug_list $200.;
input id date_a Drug_list;
format id 3. date_a mmddyy10. Drug_list $200.;
cards;
1 1/2/2018 Afatinib,Cetuximab,Dacomitinib,Erlotinib,Gefitinib,Lapatinib,Necitumumab,Osimertinib,Panitumumab
1 2/10/2019 Cetuximab,Panitumumab
2 3/3/2019 Binimetinib,Cetuximab,Cobimetinib,Idelalisib,Panitumumab,Trametinib
3 1/2/2018 Alpelisib,Everolimus,Temsirolimus
4 2/10/2019 Bosutinib,Dasatinib,Everolimus,Temsirolimus
5 3/3/2019 Afatinib,Cetuximab,Dacomitinib,Erlotinib,Gefitinib,Lapatinib,Necitumumab,Osimertinib,Panitumumab
6 1/2/2018 Binimetinib,Cetuximab,Cobimetinib,Idelalisib,Panitumumab,Trametinib
7 2/10/2019 Alpelisib,Everolimus,Temsirolimus
;;;

data B;
informat id 3. date_b mmddyy10. Drug $20.;
input id date_b Drug;
format id 3. date_b mmddyy10. Drug $200.;
cards;
1 2/01/2018 Panitumumab
1 3/02/2019 Binimetinib
1 3/28/2019 Cetuximab
2 3/23/2019 Trametinib
2 3/25/2019 Temsirolimus
3 2/01/2018 Alpelisib
5 3/21/2019 Dacomitinib
4 3/01/2019 Everolimus
6 1/17/2018 Idelalisib
7 3/02/2019 Temsirolimus
;;;


Proc sql;
Create table c as select a.*, b.date_b, b.Drug from a a, b b
Where index( a.drug_list,strip(b.drug)) > 0;
Quit;

Got the order of values wrong.

Re: Scanning text and merging

newsas007 — Fri, 10 Jul 2020 02:56:50 GMT

Although it runs and matches, This code is creating 29 rows, whereas my dataset will have the same number of rows as dataset B. It also has a couple of missing values for ID#1 and ID#2.

Re: Scanning text and merging

PGStats — Fri, 10 Jul 2020 03:03:18 GMT

Why is Cetuximab associated with the second but not the first drug list for ID=1?

Re: Scanning text and merging

smantha — Fri, 10 Jul 2020 04:46:59 GMT

What you showed in the output is this. @mkeintz , @newsas007 please notice this response

data A;
informat id 3. date_a mmddyy10. Drug_list $200.;
input id date_a Drug_list;
format id 3. date_a mmddyy10. Drug_list $200.;
cards;
1 1/2/2018 Afatinib,Cetuximab,Dacomitinib,Erlotinib,Gefitinib,Lapatinib,Necitumumab,Osimertinib,Panitumumab
1 2/10/2019 Cetuximab,Panitumumab
2 3/3/2019 Binimetinib,Cetuximab,Cobimetinib,Idelalisib,Panitumumab,Trametinib
3 1/2/2018 Alpelisib,Everolimus,Temsirolimus
4 2/10/2019 Bosutinib,Dasatinib,Everolimus,Temsirolimus
5 3/3/2019 Afatinib,Cetuximab,Dacomitinib,Erlotinib,Gefitinib,Lapatinib,Necitumumab,Osimertinib,Panitumumab
6 1/2/2018 Binimetinib,Cetuximab,Cobimetinib,Idelalisib,Panitumumab,Trametinib
7 2/10/2019 Alpelisib,Everolimus,Temsirolimus
;;;
proc sort; by id; run;

data B;
informat id 3. date_b mmddyy10. Drug $20.;
input id date_b Drug;
format id 3. date_b mmddyy10. Drug $200.;
cards;
1 2/01/2018 Panitumumab
1 3/02/2019 Binimetinib
1 3/28/2019 Cetuximab
2 3/23/2019 Trametinib
2 3/25/2019 Temsirolimus
3 2/01/2018 Alpelisib
5 3/21/2019 Dacomitinib
4 3/01/2019 Everolimus
6 1/17/2018 Idelalisib
7 3/02/2019 Temsirolimus
;;;

proc sort; by id; run;

data c;
merge b a;
by id;
if findw( drug_list,strip(drug),',') = 0 then drug_list='';
run;

Which might be wrong as you get this warning in the log

NOTE: MERGE statement has more than one data set with repeats of BY values.
NOTE: There were 10 observations read from the data set WORK.B.
NOTE: There were 8 observations read from the data set WORK.A.
NOTE: The data set WORK.C has 10 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

instead the result should be

Proc sql;
Create table c2 as select a.date_a,a.drug_list, b.date_b, b.Drug,b.id from a a right join b b
on a.id=b.id and  findw( a.drug_list,strip(b.drug),',') > 0 and (b.date_b);
Quit;

date_a	Drug_list	date_b	Drug	id
1/2/2018	Afatinib,Cetuximab,Dacomitinib,Erlotinib,Gefitinib,Lapatinib,Necitumumab,Osimertinib,Panitumumab	2/1/2018	Panitumumab	1
1/2/2018	Afatinib,Cetuximab,Dacomitinib,Erlotinib,Gefitinib,Lapatinib,Necitumumab,Osimertinib,Panitumumab	3/28/2019	Cetuximab	1
2/10/2019	Cetuximab,Panitumumab	2/1/2018	Panitumumab	1
2/10/2019	Cetuximab,Panitumumab	3/28/2019	Cetuximab	1
		3/2/2019	Binimetinib	1
3/3/2019	Binimetinib,Cetuximab,Cobimetinib,Idelalisib,Panitumumab,Trametinib	3/23/2019	Trametinib	2
		3/25/2019	Temsirolimus	2
1/2/2018	Alpelisib,Everolimus,Temsirolimus	2/1/2018	Alpelisib	3
2/10/2019	Bosutinib,Dasatinib,Everolimus,Temsirolimus	3/1/2019	Everolimus	4
3/3/2019	Afatinib,Cetuximab,Dacomitinib,Erlotinib,Gefitinib,Lapatinib,Necitumumab,Osimertinib,Panitumumab	3/21/2019	Dacomitinib	5
1/2/2018	Binimetinib,Cetuximab,Cobimetinib,Idelalisib,Panitumumab,Trametinib	1/17/2018	Idelalisib	6
2/10/2019	Alpelisib,Everolimus,Temsirolimus	3/2/2019	Temsirolimus	7

Re: Scanning text and merging

newsas007 — Fri, 10 Jul 2020 04:24:51 GMT

I edited the dataset a bit to make more sense. I added the G_type variable for the drug to match the drug list. Hope this makes more sense...

Re: Scanning text and merging

newsas007 — Fri, 10 Jul 2020 04:28:26 GMT

sorry i made that change and added the G_type associated with the specific drug list

Re: Scanning text and merging

smantha — Fri, 10 Jul 2020 04:53:31 GMT

Adding a new variable does not change the result any way unless there is a business logic associated with it or the column is on both datasets. See my earlier response. You get your desired result using a data step merge, but this is wrong.

Re: Scanning text and merging

mkeintz — Fri, 10 Jul 2020 04:35:15 GMT

@smantha

I recommend you make two changes:

add "a.id=b.id" to the where clause. This is why @newsas007 notices and excess number of cases.
consider replacing
    index( a.drug_list,strip(b.drug)) > 0
with
   findw(a.drug_list,strip(b.drug),', ') > 0

The latter will prevent inadvertently matching a substring of a drug name in the drug_list variable. It's not a problem in this sample data, but if you had a drug name=Erlotin, the INDEX function would match it to Erlotinib in the drug list.   The third argument of findw above contains a blank and a comma, telling SAS to consider those characters as word separators, and the job of FINDW is to match only whole words.