topic Symget function with the in operator in SAS Programming

Symget function with the in operator

polingjw — Tue, 28 Jun 2011 15:48:53 GMT

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?

Re: Symget function with the in operator

chang_y_chung_hotmail_com — Tue, 28 Jun 2011 16:44:20 GMT

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

Re: Symget function with the in operator

polingjw — Tue, 28 Jun 2011 17:24:46 GMT

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

Re: Symget function with the in operator

Ksharp — Wed, 29 Jun 2011 03:48:46 GMT

   %let names=Barbara James;


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

Ksharp

Re: Symget function with the in operator

polingjw — Wed, 29 Jun 2011 10:16:20 GMT

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.

Re: Symget function with the in operator

data_null__ — Wed, 29 Jun 2011 11:20:09 GMT

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;

Re: Symget function with the in operator

polingjw — Wed, 29 Jun 2011 11:37:20 GMT

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.

Re: Symget function with the in operator

Ksharp — Fri, 01 Jul 2011 01:43:41 GMT

How about :

%let names=Barbara James;


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

Ksharp

Re: Symget function with the in operator

chang_y_chung_hotmail_com — Fri, 01 Jul 2011 03:17:05 GMT

@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

Re: Symget function with the in operator

Ksharp — Mon, 04 Jul 2011 01:53:23 GMT

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