Help using Base SAS procedures

scan to see if field_A is embedded in field_B

Reply
Super Contributor
Posts: 395

scan to see if field_A is embedded in field_B

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

Super User
Super User
Posts: 7,401

Re: scan to see if field_A is embedded in field_B

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;

Super User
Posts: 17,836

Re: scan to see if field_A is embedded in field_B

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

New Contributor
Posts: 2

Re: scan to see if field_A is embedded in field_B

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;

Respected Advisor
Posts: 3,777

Re: scan to see if field_A is embedded in field_B

Find has an option to trim.

New Contributor
Posts: 2

Re: scan to see if field_A is embedded in field_B

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;

Super Contributor
Posts: 395

Re: scan to see if field_A is embedded in field_B

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..

Regular Contributor
Posts: 217

Re: scan to see if field_A is embedded in field_B

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

Occasional Contributor
Posts: 12

Re: scan to see if field_A is embedded in field_B


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.

Super Contributor
Posts: 395

Re: scan to see if field_A is embedded in field_B

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

Super User
Posts: 17,836

Re: scan to see if field_A is embedded in field_B

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.

Trusted Advisor
Posts: 1,204

Re: scan to see if field_A is embedded in field_B

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;

Super Contributor
Posts: 395

Re: scan to see if field_A is embedded in field_B

How would I include the 2 have tables ?

Trusted Advisor
Posts: 1,204

Re: scan to see if field_A is embedded in field_B

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

Super Contributor
Posts: 395

Re: scan to see if field_A is embedded in field_B

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


Ask a Question
Discussion stats
  • 19 replies
  • 515 views
  • 0 likes
  • 9 in conversation