topic Re: How to remove the space for Macro N? in SAS Programming

How to remove the space for Macro N?

ybz12003 — Wed, 28 Jun 2017 12:40:49 GMT

Hello:

I have the following program. I found the &N207 came with space and couldn't be recoganized. How to remove the space of N207? I have 200 macro N, I prefer not doing this one by one. Thanks.

proc SQL;

Select sum(case when find (name, 'nad_dx' )>0 then 1 else 0 end) into: N207 FROM &State.name;

quit;

data want;

set have (keep=nad_dx_&N207);

run;

SYMBOLGEN: Macro variable N207 resolves to 32

ERROR: nad_dx_ does not have a numeric suffix.

Re: How to remove the space for Macro N?

Astounding — Wed, 28 Jun 2017 12:43:21 GMT

Either before or after the QUIT statement:

%let n207 = &n207;

This generates leading blanks to the right of the equal sign, but %LET ignores those leading blanks.

Re: How to remove the space for Macro N?

ybz12003 — Wed, 28 Jun 2017 13:00:18 GMT

So if I have 207 macro N, could I do the following? Or I have to list them one by one?

%let n1-n207 = &n201- &n207;

Re: How to remove the space for Macro N?

Astounding — Wed, 28 Jun 2017 13:07:19 GMT

If you have 207 macro variables, you probably got them by looping through 207 SELECT statements. Just make the %LET statement part of the macro that contains the SELECT statement. Since %LET can go before the QUIT statement that ends PROC SQL, this should be relatively straightforward but you can always post the macro if you need help with that.

Re: How to remove the space for Macro N?

ybz12003 — Wed, 28 Jun 2017 13:13:05 GMT

Could you show me more details how to do it? Thanks.

Re: How to remove the space for Macro N?

Kurt_Bremser — Wed, 28 Jun 2017 13:16:03 GMT

207 (two-hundred-and-seven) macro variables? Madness. Stark raving madness. Keep your data in datasets.

Re: How to remove the space for Macro N?

Astounding — Wed, 28 Jun 2017 13:22:55 GMT

If the SELECT statement is in a macro loop, it would look like this (in simplified form):

%do i=1 %to &n;

select ... into : N&i;

%end;

If that's the case, you could always change that code to:

%do i=1 %to &n;

select ... into : N&i;

%let n&i = &&n&i;

%end;

Alternatively if you are just trying to handle 207 macro variables after the fact, embed this code in a macro:

%do i=1 %to 207;

%let n&i = &&n&i;

%end;

This has to go inside a macro because %DO is not permitted outside of a macro.

Re: How to remove the space for Macro N?

Ksharp — Wed, 28 Jun 2017 13:38:01 GMT

Select sum(case when find (name, 'nad_dx' )>0 then 1 else 0 end) into: N207 separated by ' '

Re: How to remove the space for Macro N?

Tom — Wed, 28 Jun 2017 13:48:44 GMT

First thing is to not add the spaces to begin with use the TRIMMED keyword in the select. Or if you are running a really old version of SAS use SEPARATED BY ' ' instead.

select count(*) into :N trimmed from ....

Second is add a step to re-assign the macro variable value to itself as this will remove leading/trailing spaces.

%let n=&n;

Or third since your value is an integer you can use %EVAL() where you reference the value.

data want;
  set have (keep=nad_dx_%eval(&N207));
run;

Re: How to remove the space for Macro N?

ybz12003 — Wed, 28 Jun 2017 15:13:23 GMT

Thanks for all of your great help. I wish I could do multipul 'accept solution'.