DATA Step, Macro, Functions and more

Macro variables and concatenation

Reply
N/A
Posts: 0

Macro variables and concatenation

Hi guys

I want to extract logins from table to userlist variable and substitute it to the where clause. Here is a piece of code:

%let in=work.MetaLogins;
proc sql noprint;
select Login
into :userlist separated by "','"
from ∈
quit;

%let userlist = '&userlist';
data logins;
set xml.Login;
where UserID in (&userlist);
run;

I am tired of that. Help me please to force it work.

Thanks.
Super Contributor
Posts: 260

Re: Macro variables and concatenation

Posted in reply to deleted_user
I think your problem is that LOGIN is a character variable, hence lacking quotes in the WHERE statement. When you add the external quotes in the %LET statement, the &userlist is not resolved, because simple quotes (') BLOCKS the macro resolution. Instead of adding them in the "SEPARATED BY" part, try this : combination of LEFT, TRIM (to remove trailing blanks) and QUOTE (adds quotes) functions in the SELECT clause, and only SEPARATED BY commas.
[pre]
%let in=work.MetaLogins;
proc sql noprint;
select QUOTE(LEFT(TRIM(Login)))
into :userlist separated by ","
from ∈
quit;

data logins;
set xml.Login;
where UserID in (&userlist);
run;
[/pre]
Regards.
Olivier
Ask a Question
Discussion stats
  • 1 reply
  • 167 views
  • 0 likes
  • 2 in conversation