Hello
I have a question. In my code I had a WHERE clause with IN operator and dozens of string values written inside the brackets. It wasn't very pleasant to look at, so I thought that it would be better to put these values inside a single variable and make the IN operator read them. My mainly idea was to use proc sql:
Proc sql;
select DISTINCT 'TABLE'n
into :varlist separated by '", "'
from USERSPDS.TABLE1;
quit;
%let varlist = &varlist;
Proc sql;
select DISTINCT 'LIBREF'n
into :var2list separated by '", "'
from USERSPDS.TABLE1;
quit;
%let var2list = &var2list;
It seemed to work fine and values I wanted to put inside these two variables are actually inside but I have problem:
where n1.name IN ("&varlist") and n1.libname IN ("&var2list"); <---- I thought that would work but I get an "error":
Val1", "Val2", "Val3", "Val4", "Val5", "Val6", "Val7", "Val8", "Val9", "Val10
____ ____ ____ ____ ____ ____ ____ ____ ____
49 49 49 49 49 49 49 49 49
NOTE 49-169: The meaning of an identifier after a quoted string may change in a future SAS release. Inserting white space between
a quoted string and the succeeding identifier is recommended.
That error is actually a NOTE but it stops my process.. Any idea how should I insert that white space?
Thanks in advance!
SAS is seeing ", " as the quoted string, which is not what you intended. You need to add a double quote character at the beginning and end of VARLIST (and VAR2LIST) so that the quoted strings are "Val1", then "Val2", etc.
I think this could be accomplished by changing the line
%let varlist = &varlist;
to
%let varlist = "%trim(&varlist)";
(and then leave out the quotes when you use the macro variables).
TBH I don't like macro variables, so my personal preference would be to do:
proc sql;
create table WANT as
select *
from HAVE
where strip(LIBNAME)||'.'||strip(NAME) in (select distinct strip(LIBNAME)||'.'||strip(NAME) from USERSPDS.TABLE1);
quit;
I've done it like this:
select DISTINCT catx("'",'TABLE'n ,"'")
into :varlist separated by ', '
from USERSPDS.TABLE1;
quit;
%let varlist = &varlist;
Proc sql;
select DISTINCT catx("'",'LIBREF'n,"'")
into :var2list separated by ', '
from USERSPDS.TABLE1;
quit;
%let var2list = &var2list;
where n1.name IN (&varlist) and n1.libname IN (&var2list);
SAS is seeing ", " as the quoted string, which is not what you intended. You need to add a double quote character at the beginning and end of VARLIST (and VAR2LIST) so that the quoted strings are "Val1", then "Val2", etc.
I think this could be accomplished by changing the line
%let varlist = &varlist;
to
%let varlist = "%trim(&varlist)";
(and then leave out the quotes when you use the macro variables).
One more solution - use the quote function though I'd probably use solution instead of bothering with macro variable's at all.
For example can you have a data set Class in two different libraries? The first macro solution may not return exactly what you intended in that circumstances.
Proc sql;
select DISTINCT quote('TABLE'n)
into :varlist separated by ", "
from USERSPDS.TABLE1;
quit;
%let varlist = &varlist;
Hey There...
I have used this macro many a times...
SASTechies: Put distinct values of a variable in a SAS dataset to a macro variable
I hope it helps...
Use QUOTE function and you don't need commas for IN operator.
Have you considered using something like the following?
Proc sql;
create table WANT as
select *
from HAVE
where name in (select distinct name from HAVE)
;quit;
here is how I would do it:
This code would get a list of all Tables in the Data dictionary that start with "S": NOTE: a LIST of Char variables should be quoted; a numeric list would not.
proc sql noprint;
select distinct quote(trim(memname)) into :tblList separated by ","
from sashelp.vtable
where memname like "S%: and libname eq 'MAPS';
quit;
%put &tblList;
run;
... and this is the log for the PUT statement: (always a great idea to display incrementally what is be created, so put to the log liberally).
"SALVADO2","SALVADOR","SAMERIC2","SAMERICA","SANMARI2","SANMARIN","SAUDIAR2","SAUDIARA","SERBIA"
,"SERBIA2","SINGAPO2","SINGAPOR","SLOVAKI2","SLOVAKIA","SLOVENI2","SLOVENIA","SPACIFIC","SPAIN",
"SPAIN2","SRILANK2","SRILANKA","STATES","SURINAM2","SURINAME","SWEDEN","SWEDEN2","SWITZER2","SWITZERL","SYRIA","SYRIA2"
then, you can use it successfully as a FILTER in another PROC SQL step (or within the same PROC SQL and the next SELECT statement...)
where n1.name IN ( &TBLLIST and n1.libname IN ( &NEXTLIST ); NOTE: it's already quoted, so you do not need to quote the whole list again.
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.