How to find and replace using a lookup table

[Mishka1]

Hello, I have a table with a variable that stores Where clauses for SQL queries. I need to do a find/replace (like TRANWRD functionality) to this variable. The queries are pulled out of a 3rd party system and I need to change the field names in the where clause in order to run it against our local table. I have a lookup table that contains the string to find in one variable and the string to replace it with in another.

How can I replace each field name in one table by looking it up in another table and replacing it with the corresponding variable?

For example, The Query table looks like this:

QueryWhereClause
trancd = 8  or UI = 8
(trancd = 1 and herID = 'CD') or BWW = '3bth32'
(trancd = 2 and herID = 'CD') or BWW = '4bth10'

The lookuptable looks like this:

OldField	NewField
trancd	TRAN_CD
UI	USER_INT
herID	HER_ID_VC

What I need is the QueryWhereClause table to look like this:

Lookuptbl
TRAN_CD = 8  or USER_INT = 8
(TRAN_CD = 1 and HER_ID_VC= 'CD') or HER_ID_VC= '3bth32'
(TRAN_CD = 2 and HER_ID_VC= 'CD') or HER_ID_VC= '4bth10'

Thanks for looking!

[art297 · Accepted solution]

Here is a rather brute force way of accomplishing the task:

data querry;

  informat querry $50.;

  input querry &;

  cards;

trancd = 8 or UI = 8

(trancd = 1 and herID = 'CD') or BWW = '3bth32'

(trancd = 2 and herID = 'CD') or BWW = '4bth10'

;

data lookup;

  informat old new $10.;

  input (old new) ($);

  cards;

trancd          TRAN_CD

UI          USER_INT

herID          HER_ID_VC

;

/*** get number of records in lookup dataset ***/

data _null_;

  if 0 then set lookup nobs=nobs;  /*** no need to read dataset - just metadata ***/

  CALL SYMPUT('NUMREC',nobs);      /*** put # of records into NUMREC macro var ***/

  stop;                            /*** stop, got number of records ***/

run;

/*** now read the lookup records into an array, and then read the querry records

     and modify with tranwrd ***/

data want (keep=querry);

  array Lookup(2,&numrec) $10; /*** create an array with same number of elements

                                  as there are records in amaster ***/

  i=0;

  do until (eof1);           /*** load the array with the lookup records ***/

    set lookup end=eof1;

    i+1;

    lookup(1,i)=old;

    lookup(2,i)=new;

  end;

  do until (eof2); /*** now read each querry record and apply tranwrd function***/

    set querry end=eof2;

    do i=1 to &numrec;

      querry=tranwrd(querry,strip(lookup(1,i)),strip(lookup(2,i)));

    end;

    output;

  end;

run;

[Mishka1]

Jaw dropping!

I just had to change the array length to be big enough to fit all of my lookup values in (from

"array Lookup(2,&numrec) $10" I changed $10 to $100) and I was good to go!

Strange results happen when that is too small or too big.

Thank you so much!!!

[art297]

Sometimes brute force is good!  Minimally, it can often accomplish a task which can them be optimized when/if you ever discover a better method.