topic Search columns for match in string from other table in SAS Programming

Search columns for match in string from other table

gabagotati — Mon, 19 Sep 2022 16:13:24 GMT

I have two tables- one table has 10 character variables, ID1 up to ID4. The other table is a lookup table; that one has just one variable of sales ID strings I am interested in.

What I have:

ID_1 ID_2 ID_3 ID_4

A2543 A3700 C3404 A4314

A3667 C1000 F3123 A3414

A3624 D1434 F1334 A0394

A6000 A4303 A3802 A4137

B7000 B6205 B5310 B1394

data relevant_ID;
	length salesID $3;
    input salesID;
    datalines;
A36
A37
A38
A39
A40
A41
A42
A43
A44
A45
A46
A47
A48
A49
A50
;
run;

I would like to search the table ID_1 to ID_4 to see if any of those columns include one of the values from relevant_ID. If so, I am trying to create a variable that flags whether any of those columns includes a relevant ID.

Desired output:

ID_1 ID_2 ID_3 ID_4 relevant_match

A2543 A3700 C3404 A4314 1

A3667 C1000 F3123 A3414 1

A3624 D1434 F1334 A0394 1

A6000 A4303 A3802 A4137 1

B7000 B6205 B5310 B1394 0

Re: Search columns for match in string from other table

PeterClemmensen — Mon, 19 Sep 2022 17:31:18 GMT

Are the lengths of the strings in relevant_id always constant 3?

Re: Search columns for match in string from other table

gabagotati — Mon, 19 Sep 2022 17:36:09 GMT

Yes - the string in relevant_id is always a length of 3.

Re: Search columns for match in string from other table

PeterClemmensen — Mon, 19 Sep 2022 17:46:05 GMT

Ok. Try this

data have;
input (ID_1 ID_2 ID_3 ID_4)($);
datalines;
A2543 A3700 C3404 A4314
A3667 C1000 F3123 A3414
A3624 D1434 F1334 A0394
A6000 A4303 A3802 A4137
B7000 B6205 B5310 B1394
; 

data relevant_ID;
	length salesID $3;
 input salesID;
 datalines;
A36
A37
A38
A39
A40
A41
A42
A43
A44
A45
A46
A47
A48
A49
A50
;

data want(drop = salesID);
   
   if _N_ = 1 then do;
      dcl hash h(dataset : 'relevant_ID');
      h.definekey('salesID');
      h.definedone();
      dcl hiter i('h');
   end;

   set have;
   array id id_:;
   
   salesID = '   ';
   relevant_match = 0;

   do while (i.next() = 0);
      
      do over id;
         if find(id, salesID) then do;
            relevant_match = 1;
            leave;
         end;
      end;

   end;

run;

Result:

ID_1   ID_2   ID_3   ID_4   relevant_match
A2543  A3700  C3404  A4314  1
A3667  C1000  F3123  A3414  1
A3624  D1434  F1334  A0394  1
A6000  A4303  A3802  A4137  1
B7000  B6205  B5310  B1394  0

Re: Search columns for match in string from other table

Ksharp — Tue, 20 Sep 2022 11:35:07 GMT

data have;
input (ID_1 ID_2 ID_3 ID_4)($);
datalines;
A2543 A3700 C3404 A4314
A3667 C1000 F3123 A3414
A3624 D1434 F1334 A0394
A6000 A4303 A3802 A4137
B7000 B6205 B5310 B1394
; 

data relevant_ID;
 length salesID $3;
 input salesID;
 datalines;
A36
A37
A38
A39
A40
A41
A42
A43
A44
A45
A46
A47
A48
A49
A50
;

proc sql noprint;
select distinct salesID into :salesID separated by '|' from relevant_ID;
quit;
options noquotelenmax;
data want;
set have;
length all $ 4000;
all=catx('|',of ID_:);
relevant_match=0;
if prxmatch("/&salesID./",all) then relevant_match=1;
drop all;
run;