DATA Step, Macro, Functions and more

Symget function with the in operator

Accepted Solution Solved
Reply
Regular Contributor
Posts: 171
Accepted Solution

Symget function with the in operator

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?


Accepted Solutions
Solution
‎06-28-2011 12:44 PM
Regular Contributor
Posts: 241

Re: Symget function with the in operator

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


All Replies
Solution
‎06-28-2011 12:44 PM
Regular Contributor
Posts: 241

Re: Symget function with the in operator

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
   */

Regular Contributor
Posts: 171

Re: Symget function with the in operator

Posted in reply to chang_y_chung_hotmail_com

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

Super User
Posts: 10,046

Re: Symget function with the in operator

Or

   %let names=Barbara James;


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

Ksharp

Regular Contributor
Posts: 171

Re: Symget function with the in operator

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.

Respected Advisor
Posts: 3,799

Re: Symget function with the in operator

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;

Regular Contributor
Posts: 171

Re: Symget function with the in operator

Posted in reply to data_null__

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.

Super User
Posts: 10,046

Re: Symget function with the in operator

How about :

%let names=Barbara James;


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

Ksharp

Regular Contributor
Posts: 241

Re: Symget function with the in operator

@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

Super User
Posts: 10,046

Re: Symget function with the in operator

Posted in reply to chang_y_chung_hotmail_com

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 9 replies
  • 382 views
  • 5 likes
  • 4 in conversation