topic How to find and replace using a lookup table in SAS Enterprise Guide

How to find and replace using a lookup table

Mishka1 — Wed, 26 Oct 2011 15:41:37 GMT

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!

How to find and replace using a lookup table

art297 — Wed, 26 Oct 2011 17:08:15 GMT

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;

How to find and replace using a lookup table

Mishka1 — Wed, 26 Oct 2011 18:04:23 GMT

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

How to find and replace using a lookup table

art297 — Wed, 26 Oct 2011 18:08:18 GMT

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.