topic call execute problem in SAS Programming

call execute problem

harrylui — Mon, 08 Jun 2020 11:13:16 GMT

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

Re: call execute problem

MCoopmans — Mon, 08 Jun 2020 11:46:50 GMT

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.

Re: call execute problem

Patrick — Mon, 08 Jun 2020 12:29:57 GMT

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";

Re: call execute problem

Reeza — Mon, 08 Jun 2020 14:09:40 GMT

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