BookmarkSubscribeRSS Feed
podarum
Quartz | Level 8

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

19 REPLIES 19
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Reeza
Super User

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

gfisher
Calcite | Level 5

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;

data_null__
Jade | Level 19

Find has an option to trim.

gfisher
Calcite | Level 5

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;

podarum
Quartz | Level 8

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

jwillis
Quartz | Level 8

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

nathan_owens
Obsidian | Level 7


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.

podarum
Quartz | Level 8

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

Reeza
Super User

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.

stat_sas
Ammonite | Level 13

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;

podarum
Quartz | Level 8

How would I include the 2 have tables ?

stat_sas
Ammonite | Level 13

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

podarum
Quartz | Level 8

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


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 19 replies
  • 1350 views
  • 0 likes
  • 9 in conversation