BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Dontik
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
RickAster
Obsidian | Level 7

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

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

DBailey
Lapis Lazuli | Level 10

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

RickAster
Obsidian | Level 7

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

Reeza
Super User

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;

data_null__
Jade | Level 19

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;
timm
Calcite | Level 5

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;

fredmayer23
Fluorite | Level 6

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.  

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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