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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.