call execute problem

[harrylui]

hi sas experts,

here is my original script: 

%macro sqlmerchant(name=,test=);
if &test then do;
Cleaned_flag="Y";
NAME="&name";
end;

%mend sqlmerchant;
data TESTING2;
set TESTING;
format
name $CHAR255.;
%sqlmerchant(name=A BAKERY CHATERAISE, test=indexw(NEW,"A BAKERY CHATERAISE") & "Merchant Category"n in ("Miscellaneous Stores----Quick Payment Service-Fast Food Restaurants") );
%sqlmerchant(name=A BAKERY CHATERAISE, test=indexw(NEW,"A BAKERY CHATERAISE") & "Merchant Category"n in ("Retail Outlet Services----Bakeries") );

but i shorten my script by removing repeat marco

so i build up a keyword inventory like this:

final_name2	Merchant Category	NEW_NAME2
A A	Professional Services and Membership Organizations----Schools and Educational Services - Not Elsewhere Classified	A A DRIVER TRAINING
A A OPTICAL HONG KONG	Professional Services and Membership Organizations----Opticians, Optical Goods and Eyeglasses	A A OPTICAL HONG KONG
A A OPTICAL YUEN LONG	Professional Services and Membership Organizations----Opticians, Optical Goods and Eyeglasses	A A OPTICAL HONG KONG

can anyone help me to change it into this fore using call execute?

%macro sqlmerchant(name=,test=);
if &test then do;
Cleaned_flag="Y";
NAME="&name";
end;

%mend sqlmerchant;

data TESTING2;
set TESTING;
format
name $CHAR255.;
call execute('keyword2.'||strip(name)||';
set keyword2;
test=indexw(final_name2,'||strip(name)||') & "Merchant Category"n in ('||strip(name)||') );
run;');

thanks in advance

harry

[MCoopmans]

Hello Harry,

Statements generated with CALL EXECUTE will only be executed AFTER the datastep that generates them is finished. Unlike macro statements that are executed BEFORE. 

Perhaps you should elaborate a little more about what you are trying to accomplish , and perhaps pass along some sample data in a datastep cards statement so we can replicate what you are doing.

Regards,

Mathias.

[Patrick]

From the looks of it you just want to create a new variable where you replace a very long string with a shorter string. If so then consider to create a format.

If you also add an OTHER case to the format where the label is missing then you can also query the result and set your flag (set to Y if the new variable is not missing).

name=put(have, $myformat.);

if not missing(name) then Cleaned_flag="Y";

[Reeza]

Any reason you didn't use arrays instead of a macro here?