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

I want to specify a list of values using the in operator in the where statement of a data step view.  I have a macro variable that resolves to the list of values.  The value of this macro variable is not constant, so the symget/resolve function needs to be used to obtain the value of the macro variable during execution time whenever the view is referenced.  The problem is that the in operator expects a list of quoted strings, not a function such as symget.

Here is a simplified example.  The following code works fine for one name:

data students/view=students;

      set sashelp.class;

      where name = symget('name');

run;

%let name=Barbara;

proc print data=students; run;

%let name=Jane;

proc print data=students; run;

However, suppose I have a single macro variable that resolves to a list of students such as: 

%let names=Barbara James;

This modified code will produce an error:

data students/view=students;

      set sashelp.class;

      where name in (symget('names'));

run;

Can anyone think of a simple code modification or an alternative view that will make this problem work?

1 ACCEPTED SOLUTION

Accepted Solutions
chang_y_chung_hotmail_com
Obsidian | Level 7

You may have to give up the in operator. Code snipet below utilizes the indexw function, instead.

   data students/view=students;
         set sashelp.class;
         where indexw(symget('names'), name, " ");
   run;

   %let names=Barbara James;
   proc print data=students;
   run;
   /* on lst
        Obs     Name      Sex    Age    Height    Weight
          1    Barbara     F      13     65.3       98
          2    James       M      12     57.3       83
   */

View solution in original post

9 REPLIES 9
chang_y_chung_hotmail_com
Obsidian | Level 7

You may have to give up the in operator. Code snipet below utilizes the indexw function, instead.

   data students/view=students;
         set sashelp.class;
         where indexw(symget('names'), name, " ");
   run;

   %let names=Barbara James;
   proc print data=students;
   run;
   /* on lst
        Obs     Name      Sex    Age    Height    Weight
          1    Barbara     F      13     65.3       98
          2    James       M      12     57.3       83
   */

polingjw
Quartz | Level 8

Yes, that’s exactly what I was looking for.  Thanks!

Ksharp
Super User

Or

   %let names=Barbara James;


data students/view=students;
set sashelp.class;
 where name in (  %sysfunc(tranwrd(%sysfunc(quote(&names)),%str( ),%str(" ")) )   );
   run;

Ksharp

polingjw
Quartz | Level 8

Thank you for replying Ksharp, but this solution does not solve the problem.  The problem is not the quotes; rather it is a timing issue.  I need for the macro variable to be resolved at execution time, thus the use of the SYMGET function. 

Chang’s solution works well, but if anyone can think of a way to utilize the in operator, that would be even better.  The reason the in operator would be better is because I’m actually working with a SAS view that is based on a SAS/ACCESS view.  The INDEXW function is not passed to the DBMS for processing, where a list of values specified via the in operator is passed to the DBMS.  Thus the INDEXW function runs slower.  However, I’m not confident that such a solution is possible. 

If the INDEXW solution becomes too slow, I might have to create a macro containing the code to create the view (using the in operator) and then call the macro to re-compile the view before each use.  This would not be very hard to implement, I just wanted to know if there was a better way.

Thanks  again.

data_null__
Jade | Level 19

That all sounds very complicated. 

Could you adapt this form of the IN operator?  It may have the same pass through problems as the INDEXW function.  Which of course would also be a problem for SYMGET if it worked in IN operator as it would not be passed through.

proc sql;

   select * from sashelp.class

      where name in(select name from names);

   quit;

   run;

polingjw
Quartz | Level 8

You are right, the SYMGET function would not be passed to the DBMS either.  Therefore, even if somehow I could use the in operator in conjunction with SYMGET, the execution time would not be any quicker. 

Thanks for the SQL suggestion.  I would rather keep using a DATA step because the actual view is a bit more involved and includes FIRST. and LAST. variables.

I don’t think that re-compiling the code before each use is that complicated.  For example:

%macro compile;

  data students/view=students;

  set sashelp.class;

    where name in (&names);

  run;

%mend;

%let names="Barbara" "James";

%compile

proc print data=students; run;

%let names="Henry" "Janet";

%compile

proc print data=students; run;

Thanks again to everyone for the great suggestions.

Ksharp
Super User

How about :

%let names=Barbara James;


data students/view=students;
set sashelp.class;
 where symget('names') contains trim(name);
   run;

Ksharp

chang_y_chung_hotmail_com
Obsidian | Level 7

@Ksharp: You have to match word boundaries as well, whenever you want to match words. See below for one way of doing this.

Otherwise you may end up matching a partial word as if it was a full word. For instance, your view could end up including Barbara's observation when the name is, say, "Ba".

  %let names=Barbara James;
  data students/view=students;
      set sashelp.class;
      where cat(" ", trim(symget('names')), " ")
         contains cat(" ", trim(name), " ");
  run;

Message was edited by: Chang Y. Chung -- changed cats to cat

Ksharp
Super User

Your code is wrong. Function cats() has already deleted the first and last white blank.

Solution is :

data class;
 set sashelp.class end=last;
 output;
 if last then do;
               name=' Bar Pe ';output;
               name=' Bar ';output;
              end;
run;

%let names=Barbara James;
  data students/view=students;
      set class;
      where " "||symget('names')||" "
         contains  " "||strip(name)||" ";
  run;




Or using like operator    
" "||symget('names')||" " like "% "||strip(name)||" %"

Ksharp

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
  • 9 replies
  • 1496 views
  • 5 likes
  • 4 in conversation