Macro variables and concatenation

deleted_user — Tue, 24 Jun 2008 10:10:22 GMT

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 &in;
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.

Re: Macro variables and concatenation

Olivier — Tue, 24 Jun 2008 10:29:39 GMT

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 &in;
quit;

data logins;
set xml.Login;
where UserID in (&userlist);
run;
[/pre]
Regards.
Olivier

topic Macro variables and concatenation in SAS Programming

Macro variables and concatenation

Re: Macro variables and concatenation