topic Re: Choosing values from colums not matching in SAS Programming

Choosing values from colums not matching

Q1983 — Wed, 23 Jan 2019 15:32:11 GMT

Lets say I have a dataset that produces two columns

ATTY FIRM

Scott Scott and Wagner

Hampshire Mills and Walker

Beasley Jones and Beasley

In this case I want to have a flag that says if any part of the name DOES NOT MATCH (ATTY and FIRM) then put a 'Y' in a flag called Choose, else do not choose it. So in this case only Hampshire Mills and Walker would get chosen. Is there a way to do this?

ATTY FIRM choose

Scott Scott and Wagner

Hampshire Mills and Walker Y

Beasley Jones and Beasley

Re: Choosing values from colums not matching

Kurt_Bremser — Wed, 23 Jan 2019 15:49:22 GMT

Use the findw() function:

data have;
infile cards dlm=',';
input atty :$30. firm :$30.;
cards;
Scott,Scott and Wagner
Hampshire,Mills and Walker
Beasley,Jones and Beasley
;
run;

data want;
set have;
choose = ifc(findw(firm,strip(atty)),' ','Y');
run;

Note the function that strips the trailing (and/or leading) blanks from atty.

Re: Choosing values from colums not matching

novinosrin — Wed, 23 Jan 2019 15:58:48 GMT


data have;
infile cards dlm=',';
input atty :$30. firm :$30.;
cards;
Scott,Scott and Wagner
Hampshire,Mills and Walker
Beasley,Jones and Beasley
;
run;


data want;
set have;
if not indexw(firm,strip(atty)) then choose='Y';
run;

Re: Choosing values from colums not matching

Q1983 — Thu, 24 Jan 2019 21:13:13 GMT

Thanks for your input here. I was wondering why it did not work exactly as planned
data have;
infile cards dlm=',';
input atty :$30. firm :$30.;
cards;
Scott,Scott and Wagner
Hampshire,Mills and Walker
Beasley,Jones and Beasley
Beasley,Jones and Bonds
;
run;

data want;
set have;
choose = ifc(findw(firm,strip(atty)),' ','Y');
run;
For the last one Beasley,Jones and Bonds I would get a 'Y' because it see the 'B' which is a character match. Is there a way to force it to look at the first 2 or 3 characters. In this example I would want this last entry NOT to be a match

Re: Choosing values from colums not matching

Q1983 — Thu, 24 Jan 2019 21:16:13 GMT

Lets say the last entry Beasley,Jones and Beasley was Beasley,Jones and Bond instead.

I would still get a 'Y' for a match.

Is there a way to get this to look at the first 2 or 3 characters. In that case the last entry would not be a match which is what I would want in this case. The code you provided is effective however if the first char is a match in either column we get a 'Y' which is not what I would want

Re: Choosing values from colums not matching

novinosrin — Thu, 24 Jan 2019 21:26:43 GMT

Can you modify the sample input and the output for the sample plz

Re: Choosing values from colums not matching

Q1983 — Thu, 24 Jan 2019 21:31:53 GMT

data have;

infile cards dlm=',';

input atty :$30. firm :$30.;

cards;

Scott,Scott and Wagner

Hampshire,Mills and Walker

Beasley,Jones and Bond

;

run;

data want;

set have;

if not indexw(firm,strip(atty)) then choose='Y';

run;

So in this case because in the last entry the "B" in Beasley happens to match the "B" in Bonds, we would get a 'Y'. However is there a way to get the code to look at the first 2 or 3 char and apply the same logic. In this case since Beasley is not Bond I would want this to not show a 'Y' for a match, currently it does

Re: Choosing values from colums not matching

novinosrin — Thu, 24 Jan 2019 21:45:05 GMT

Hi @Q1983 The Y is case of non matches both my code and Kurt's.

The result that you see below in the 2nd and 3rd record being 'Y' is a result of the condition "not indexw" (note the not or in other words false positives. So basically, Y is a result of non-matches and not matches.

This meets your original question where you wrote ="In this case I want to have a flag that says if any part of the nameDOES NOT MATCH(ATTY and FIRM) then put a 'Y' in a flag called Choose, else do not choose it" Kindly review

atty	firm	choose
Scott	Scott and Wagner
Hampshire	Mills and Walker	Y
Beasley	Jones and Bond	Y

@Q1983 wrote:

So in this case because in the last entry the "B" in Beasley happens to match the "B" in Bonds, we would get a 'Y'. However is there a way to get the code to look at the first 2 or 3 char and apply the same logic. In this case since Beasley is not Bond I would want this to not show a 'Y' for a match, currently it does