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.
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;
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.
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;
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.
Could you show me more details how to do it? Thanks.
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.
207 (two-hundred-and-seven) macro variables? Madness. Stark raving madness. Keep your data in datasets.
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;
Thanks for all of your great help. I wish I could do multipul 'accept solution'.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.