topic Re: substr and length in macro variable in SAS Programming

substr and length in macro variable

bhu — Thu, 28 Feb 2019 01:56:13 GMT

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.

Re: substr and length in macro variable

ChrisNZ — Thu, 28 Feb 2019 02:08:19 GMT

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.

Re: substr and length in macro variable

ScottBass — Thu, 28 Feb 2019 03:41:35 GMT

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

Re: substr and length in macro variable

ScottBass — Thu, 28 Feb 2019 03:46:40 GMT

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.

Re: substr and length in macro variable

Satish_Parida — Thu, 28 Feb 2019 05:13:49 GMT

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.

Re: substr and length in macro variable

bhu — Thu, 28 Feb 2019 17:19:27 GMT

%superq works like magic. Thanks.

Re: substr and length in macro variable

bhu — Thu, 28 Feb 2019 17:21:51 GMT

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

Re: substr and length in macro variable

ScottBass — Thu, 28 Feb 2019 21:10:46 GMT

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

Scroll down to the tip.