substr and length in macro variable

[bhu]

I have a situation where I got field names for sql join condition but with a comma at last. I want to remove the last comma. The field_names1 is calculated generically that it has not quotes.

%let field_names1=a.field1=b.field1 and a.field1=b.field2,;
%put &field_names1;
%let field_names=%substr(&field_names1,1,%length(&field_names1)-1);

%put &field_names;

it produces error:

ERROR: Macro function %SUBSTR has too many arguments.

[ChrisNZ · Accepted solution]

Like this?

%let field_names=%substr(%superq(field_names1),1,%length(%superq(field_names1))-1);
%put &field_names;

I would do this instead:

%let field_names=%sysfunc(compress(%superq(field_names1),%str(,)));
%put &field_names;

I like using function %superq(). 

It allows to blissfully sidestep many issues with problematic characters in macro variables.  

[ScottBass]

I second @ChrisNZ 's recommendation of %superq, which I often use if I don't know the structure of the macro variable input (such as a generic macro that may be called by end users). 

But, per my other post, I recommend you don't put commas in your macro variables at all, esp. those that may serve as parameters to other macros or macro functions.

P.S.:  Your problem is that that commas in your macro variable are passed as syntax to the %substr() macro function, which then thinks you're passing in too many parameters to that function.

[bhu]

%superq works like magic. Thanks.

[ScottBass]

My own approach to this is, I don't include "syntax" with "data".  In other words, don't include commas, quotation marks, etc, etc in with your macro variables.  Especially commas - you're only going to confuse the macro tokenizer without convoluted quoting that you don't have to do.

Instead, "inject" the syntax into your code at "run time".

For this I use a couple macros:

https://github.com/scottbass/SAS/blob/master/Macro/seplist.sas (originally written by Richard Devenezia)

https://github.com/scottbass/SAS/blob/master/Macro/squote.sas (originally written by "Tom") (useful for RDBMS explicit passthrough that requires single quoted literals)

See the macro header for use cases and original author attribution.

So, instead of:

%let list = "foo","bar","blah";
data foo;
   set bar;
   where var in (&list);
run;

I use:

%let list = foo bar blah;
data foo;
   set bar;
   where var in (%seplist(&list,nest=qq));
run;

Hope this helps...

[Satish_Parida]

This works.

%let field_names1=%str(a.field1=b.field1 and a.field1=b.field2,);
%put &=field_names1;

data _null_;
orginal_string="&field_names1.";
result_string=substr(strip(orginal_string),1,length(strip(orginal_string))-1);
call symput('field_names',result_string);
run;

%put &=field_names;

Please let us know if it worked for you.

[bhu]

Hi Satish,
Your solution works fine in a datastep. Thanks.
Is there any difference between &variable. and &=variable?

[ScottBass]

http://support.sas.com/documentation/cdl/en/mcrolref/62978/HTML/default/viewer.htm#n189qvy83pmkt6n1b...

Scroll down to the tip.