topic Re: scan to see if field_A is embedded in field_B in SAS Procedures

scan to see if field_A is embedded in field_B

podarum — Thu, 03 Apr 2014 15:01:06 GMT

I tried to use scan, but not getting it to work. How can I check to see if a field is embedded into another field.. But both fields can be any length.

For example

HAVE:

Field_A Field_B
2345556 00084623455569485

364758 48583364758938

2347652 48563048967030

7842 9489028457659

22386 0938422386003848

WANT:

Field_A Field_B Match

2345556 00084623455569485 1

364758 48583364758938 1

2347652 48563048967030 0

7842 9489028457659 0

22386 0938422386003848 1

thank you

Re: scan to see if field_A is embedded in field_B

RW9 — Thu, 03 Apr 2014 15:24:49 GMT

Hi,

Try the index function which returns the first position of a string or 0 if not found:

data want;

set have;

if index(field_b,field_a)>0 then match=1;

else match=0;

run;

Re: scan to see if field_A is embedded in field_B

Reeza — Thu, 03 Apr 2014 15:36:35 GMT

Find function will return a 0/1.

match=find(field_b, field_a);

Or using an index function:

match=index(field_b,field_a)>0;

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

Re: scan to see if field_A is embedded in field_B

gfisher — Thu, 03 Apr 2014 16:21:12 GMT

Use the TRIM function to remove spaces before and after the variables helps.

DATA HAVE;

INPUT Field_A $10. Field_B $21. ;

DATALINES;

2345556 00084623455569485

364758 48583364758938

2347652 48563048967030

7842 9489028457659

22386 0938422386003848

RUN;

DATA WANT;

SET HAVE;

IF INDEX(TRIM(field_b),TRIM(field_a))>0 THEN MATCH=1;

ELSE MATCH=0;

MATCH2=FIND(TRIM(field_b), TRIM(field_a));

RUN;

PROC PRINT DATA=WANT;

RUN;

Re: scan to see if field_A is embedded in field_B

data_null__ — Thu, 03 Apr 2014 16:25:11 GMT

Find has an option to trim.

Re: scan to see if field_A is embedded in field_B

gfisher — Thu, 03 Apr 2014 16:37:33 GMT

You are correct. Find has an option to trim.

DATA HAVE;

INPUT Field_A $10. Field_B $21. ;

DATALINES;

2345556 00084623455569485

364758 48583364758938

2347652 48563048967030

7842 9489028457659

22386 0938422386003848

RUN;

DATA WANT;

SET HAVE;

IF INDEX(TRIM(field_b),TRIM(field_a))>0 THEN MATCH=1;

ELSE MATCH=0;

MATCH2=FIND(TRIM(field_b), TRIM(field_a));

MATCH3=FIND(field_b,field_a,'T');

RUN;

PROC PRINT DATA=WANT;

RUN;

Re: scan to see if field_A is embedded in field_B

podarum — Thu, 03 Apr 2014 19:17:22 GMT

Hey All, none of these worked for me..

Here's an example

Field B : 194251208783001

Field A : 5120

Does it matter if one is numeric and the other text..I tried all combos..

Re: scan to see if field_A is embedded in field_B

jwillis — Thu, 03 Apr 2014 19:28:10 GMT

I believe that Scan, Index and Find all work on Character variables. I know for certain that Find only works on character variables. Convert your number to a character version of the number then use the previous functions and see how that works.

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a002267763.htm

Re: scan to see if field_A is embedded in field_B

nathan_owens — Thu, 03 Apr 2014 20:04:43 GMT

You are precisely right. Because of how numbers are displayed, we often forget that it is stored as a floating decimal. This is easy to address.

match = index (Field_B, trim(left(put(Field_A,best.)))) > 0;

By using an expression, it will return a 1 if true, 0 if false. So you don't even need an else statement.

Re: scan to see if field_A is embedded in field_B

podarum — Tue, 10 Jun 2014 20:20:19 GMT

Is there a way to do this from 2 separate datasets, with no matching key between the 2 ? So field A is in one dataset and feild B is in another. thanks

Re: scan to see if field_A is embedded in field_B

Reeza — Tue, 10 Jun 2014 21:45:20 GMT

SQL Cross Join, with the check in the where clause?

Regardless you'll have to check every field in A against all fields in B. This is time intensive.

Re: scan to see if field_A is embedded in field_B

stat_sas — Tue, 10 Jun 2014 22:02:28 GMT

Try this.

proc sql;

create table want as

select *,case when Field_B contains strip(Field_A) then 1 else 0 end as match

from have;

quit;

Re: scan to see if field_A is embedded in field_B

podarum — Wed, 11 Jun 2014 13:43:44 GMT

How would I include the 2 have tables ?

Re: scan to see if field_A is embedded in field_B

stat_sas — Wed, 11 Jun 2014 13:53:02 GMT

Can you please give more info on this? What do you mean by 2 have tables?

Re: scan to see if field_A is embedded in field_B

podarum — Wed, 11 Jun 2014 13:59:32 GMT

Field_A is in DATASET_Table_A and Field_B is in DATASET_Table_B. And I want to check if any of the fields_A are somehow nested in any of the fields_B. Thanks.

HAVE:

TABLE_A TABLE_B

Field_A Field_B
2345556 00084623455569485

364758 48583364758938

2347652 48563048967030

7842 9489028457659

22386 0938422386003848

Re: scan to see if field_A is embedded in field_B

stat_sas — Wed, 11 Jun 2014 14:13:21 GMT

Try this for the desired output.

Thanks,

proc sql;

create table want as

select * from TABLE_A,TABLE_B

where Field_B contains strip(Field_A);

quit;

Re: scan to see if field_A is embedded in field_B

Ksharp — Wed, 11 Jun 2014 14:13:36 GMT

CODE NOT TESTED.

proc sql;

create table want as

select *

from A,B

where Field_B contains strip(Field_A ) ;

quit;

Xia Keshan

Re: scan to see if field_A is embedded in field_B

podarum — Wed, 11 Jun 2014 18:29:48 GMT

Thanks all.. it worked perfectly.

Re: scan to see if field_A is embedded in field_B

podarum — Wed, 18 Jun 2014 01:46:21 GMT

Can anyone help with what I'm doing wrong here (below), I'm basically trying to create 2 new fields if the match criteria happens with Mortgage_Number_3 and another with Mortgage_Number_5. Put a 1 if match and a 0 if not.. thanks

proc sql inobs=100;

create table ABC.TEST_ME_2011_3 as

select a._ID, b.Mortgage_Number_3

from ABC.New_claims_jun10 b, KY.Loan_default_prep30nov2011 a

where a.AR_ID contains strip(b.Mortgage_Number_3) then 1 else 0 as short

where a.AR_ID contains strip(b.Mortgage_Number_5) then 1 else 0 as long;

quit;

Re: scan to see if field_A is embedded in field_B

stat_sas — Wed, 18 Jun 2014 02:21:20 GMT

Use case statement in select portion of the query to create new variables based on some condition

case when a.AR_ID contains strip(b.Mortgage_Number_3) then 1 else 0 end as short

case when a.AR_ID contains strip(b.Mortgage_Number_5) then 1 else 0 end as long