How to put a list of few values inside a single variable?

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

How to put a list of few values inside a single variable?

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!


Accepted Solutions
Solution
‎10-16-2014 12:17 PM
Contributor
Posts: 22

Re: How to put a list of few values inside a single variable?

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).

View solution in original post


All Replies
Super User
Super User
Posts: 7,727

Re: How to put a list of few values inside a single variable?

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;

Super Contributor
Posts: 578

Re: How to put a list of few values inside a single variable?

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);

Solution
‎10-16-2014 12:17 PM
Contributor
Posts: 22

Re: How to put a list of few values inside a single variable?

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).

Super User
Posts: 19,188

Re: How to put a list of few values inside a single variable?

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;

Regular Learner
Posts: 1

Re: How to put a list of few values inside a single variable?

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...

Respected Advisor
Posts: 3,790

Re: How to put a list of few values inside a single variable?

Use QUOTE function and you don't need commas for IN operator.

proc sql;
  
select name from sashelp.class where name in('Alice' 'John');
   quit;
  
run;
N/A
Posts: 1

Re: How to put a list of few values inside a single variable?

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;

Occasional Contributor
Posts: 5

Re: How to put a list of few values inside a single variable?

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.  

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 712 views
  • 3 likes
  • 9 in conversation