topic Re: Match Observations in New SAS User

Match Observations

dapenDaniel — Mon, 28 Jan 2019 17:07:43 GMT

Hello.

I have two data files. File A contains some company names and File B is the full list of company names. The variables are following below.

File A: ID, Name

File B: Name1, Name2, Name3 (3 names are for the same company but different expression. For example, Name1 = IBM and Name 2 = International Business Machines)

What I want is to search names from File A in File B. If names in File A can match one of the names in File B, then a new variable called NewName is created in File A and NewNames equals to Name1 from File B.

Can anyone tell me what code I need to use? Thanks.

For example

File A

ID Name

01 IBM

02 Apple

File B

Name1 Name 2 Name 3

International Business Machines IBM IBM Corp.

Apple Apple Inc. AAPL

Google LLC GOOGL Google

Expected File

ID Name NewName (equal to Name1)

01 IBM International Business Machines

02 Apple Apple

Re: Match Observations

novinosrin — Mon, 28 Jan 2019 16:51:54 GMT

Can you post a sample plz of both of your files that will help lazy people like me to also try

Re: Match Observations

dapenDaniel — Mon, 28 Jan 2019 17:08:19 GMT

I have added an example. Thanks.

Re: Match Observations

novinosrin — Mon, 28 Jan 2019 17:14:00 GMT


data a;
input id $ name $;
cards;
01        IBM
02        Apple
;

data b;
input (name1-name3) ( &:$50.);
cards;
International Business Machines                            IBM                                          IBM Corp.
Apple                                                       Apple Inc.                                     AAPL
Google LLC                                               GOOGL                                      Google      
;

proc sql;
create table want as
select a.*,b.name1 as new_name
from a a left join b b
on a.name=b.name1 or a.name=b.name2 or a.name=b.name3;
quit;

Re: Match Observations

kiranv_ — Mon, 28 Jan 2019 18:11:11 GMT

one more way

proc sql;
select id , name, name1
from a
left join
b
on index(catx(',', name1, name2, name3), trim(name)) gt 0;

Re: Match Observations

novinosrin — Mon, 28 Jan 2019 18:25:11 GMT

so will contains i think

proc sql;

select id , name, name1
from a
left join
b
on catx(',', name1, name2, name3) contains trim(name);
quit;

Re: Match Observations

kiranv_ — Mon, 28 Jan 2019 18:32:48 GMT

both of them should work.

Re: Match Observations

dapenDaniel — Mon, 28 Jan 2019 19:00:44 GMT

There are some duplicates in File B.

How can I remove these duplicates when I match these two files?

Thanks.

Re: Match Observations

novinosrin — Mon, 28 Jan 2019 19:02:51 GMT

Do you mean like this?



data a;
input id $ name $;
cards;
01        IBM
02        Apple
;

data b;
input (name1-name3) ( &:$50.);
cards;
International Business Machines                            IBM                                          IBM Corp.
International Business Machines                            IBM                                          IBM Corp.
International Business Machines                            IBM                                          IBM Corp.
Apple                                                       Apple Inc.                                     AAPL
Apple                                                       Apple Inc.                                     AAPL
Google LLC                                               GOOGL                                      Google      
;
proc sql;
create table want as
select distinct a.*,b.name1 as new_name
from a a left join b b
on a.name=b.name1 or a.name=b.name2 or a.name=b.name3;
quit;

Re: Match Observations

SuryaKiran — Mon, 28 Jan 2019 19:24:46 GMT

You may need to add Case expression to @novinosrin solution to add the name from table A if no match found in table B

proc sql;
create table want as
select a.*,
		case when b.name1 is null then a.name 
			else b.name1 end as new_name
from a a 
left join b b
on a.name=b.name1 or a.name=b.name2 or a.name=b.name3;
quit;

Re: Match Observations

novinosrin — Mon, 28 Jan 2019 19:37:37 GMT

@SuryaKiran Good forward thinking, however personally i prefer to take advantage of coalsece ANSI or proc sql's proprietary version coalescec. Simpler, easy and more efficient

select distinct a.*,coalsecec(name1,a.name) as new_name

Re: Match Observations

Cynthia_sas — Mon, 28 Jan 2019 20:04:36 GMT

Hi:

As another example of a lookup technique, my tendency would be to make a user-defined format from the file with the name1-name3 and then to use that format when reading data file A to make the NEWNAME variable.

Something like this:


data fmt_examp(keep=fmtname start end label);
  length start $50 end $50 label $50 ;
  retain fmtname '$namef' type 'C';
length name1 name2 name3 $50;
infile datalines dlm=',' dsd;
input name1 $ name2 $ name3 $;
label=name1;
start=name1; end=name1;
output;
start=name2; end=name2;
output;
start=name3; end=name3;
output;
datalines;
"International Business Machines","IBM","IBM Corp."
"Apple","Apple Inc.","AAPL"
"Google LLC","GOOGL","Google"      
;
run;

proc format cntlin=fmt_examp fmtlib;
run;

data a;
length name newname $50;
input id $ name $;
newname = put(name,$namef.);
cards;
01        IBM
02        Apple
;
run;

proc print data=a;
title 'Using format for lookup';
run;

(There's always more than 1 way to do things in SAS.)

Cynthia

Re: Match Observations

dapenDaniel — Tue, 29 Jan 2019 06:43:42 GMT

I mean duplicate like this:

File A

ID Name

01 IBM

02 Apple

File B

Name1 Name 2 Name 3

International Business Machines IBM IBM Corp.

Apple Apple Inc. AAPL

AAPL Apple Apple INC.

Google LLC GOOGL Google

Expected File

ID Name NewName (equal to Name1)

01 IBM International Business Machines

02 Apple Apple

02 Apple AAPL

I hope the expected file can list all possible "Name1" in File B for "Names" in File A.

Is it possible to make it?

Re: Match Observations

novinosrin — Tue, 29 Jan 2019 06:55:02 GMT

Hi @dapenDaniel The existing code accomplishes just that. Here is another test


data a;
input id $ name $;
cards;
01        IBM
02        Apple
;

data b;
input (name1-name3) ( &:$50.);
cards;
International Business Machines                            IBM                                          IBM Corp.
Apple                                                       Apple Inc.                                     AAPL
AAPL                                                         Apple                                        Apple INC.
Google LLC                                               GOOGL                                      Google     
;

proc sql;
create table want as
select a.*,b.name1 as new_name
from a a left join b b
on a.name=b.name1 or a.name=b.name2 or a.name=b.name3;
quit;

proc print noobs;run;

Result

01    IBM      International Business Machines
02    Apple    Apple
02    Apple    AAPL

Re: Match Observations

dapenDaniel — Tue, 29 Jan 2019 07:16:51 GMT

Thank you so much for your timely reply!!!

Sorry I have another questions. I found that in File B, there are other weird data format

File A

ID Name

01 IBM

02 Apple

03 bcd

File B

Name1 Name 2 Name 3

International Business Machines IBM IBM Corp.

Apple Apple Inc. AAPL

AAPL Apple Apple Inc

Google LLC GOOGL Google

abc abc | bcd bcd | abc | aef

Expected File

ID    Name      NewName
01    IBM      International Business Machines
02    Apple    Apple
02    Apple    AAPL
03    bcd      abc

For Name3 in File B, there are several names that are separate by "|". I hope that as long as the observation contains the same part (bcd in Name3), SAS helps me to get its corresponding Name1. It is unnecessary to be exactly the same.

Is it possible to do that?

Thank you very much!

Re: Match Observations

novinosrin — Tue, 29 Jan 2019 07:28:18 GMT

Switch to contains logic


data a;
input id $ name $;
cards;
01        IBM
02        Apple
03        bcd
;

data b;
input (name1-name3) ( &:$50.);
cards;
International Business Machines                            IBM                                          IBM Corp.
Apple                                                       Apple Inc.                                     AAPL
AAPL                                                        Apple                                          Apple Inc
Google LLC                                               GOOGL                                         Google 
abc                                                         abc | bcd                                      bcd | abc | aef
;
proc sql;
create table want as
select id , name, name1
from a
left join
b
on catx('|', name1, name2, name3) contains trim(name);
quit;