topic Re: How to put a list of few values inside a single variable? in SAS Programming

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

Dontik — Thu, 16 Oct 2014 14:50:26 GMT

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!

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

RW9 — Thu, 16 Oct 2014 14:58:36 GMT

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;

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

DBailey — Thu, 16 Oct 2014 15:02:09 GMT

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

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

RickAster — Thu, 16 Oct 2014 16:17:30 GMT

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;

%let varlist = "%trim(&varlist)";

(and then leave out the quotes when you use the macro variables).

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

Reeza — Thu, 16 Oct 2014 19:24:09 GMT

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;

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

tanuja — Thu, 16 Oct 2014 21:21:30 GMT

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

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

data_null__ — Thu, 16 Oct 2014 22:05:08 GMT

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;

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

timm — Fri, 17 Oct 2014 01:30:34 GMT

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;

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

fredmayer23 — Fri, 17 Oct 2014 14:29:27 GMT

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.