topic Re: Filter dataset & variable by a value list of a macro variable in SAS Programming

Filter dataset & variable by a value list of a macro variable

coj — Thu, 25 Jun 2020 10:01:19 GMT

Hello,

i need some assistance to resolve the following problem, please. I want to save a value list in a macro variable (&teams), which then is used for a comparison with the values of a variable (mapIDname) from a different dataset. The final dataset (a2) should remain only information regarding the values of the macro variable. Hope the code makes the aim of this effort clearer. Can anyone help to work my code? I guess something is missing. Thank you for any suggestion! Chris.

Betreff: Filter dataset & variable by a value list of a macro variable

coj — Thu, 25 Jun 2020 10:11:18 GMT

* Save value list in macro variable ;

proc sql;
select distinct team into :teams
from sashelp.baseball;
quit;

%put &teams;

* List of interest ;

data a1; set sashelp.gcstate; run;

* Filter list of interest by &teams;

data a2(where=(select=1)); set a1;
select=0;
if mapIDname IN ('&teams') then select=1; /* no working */
run;

Betreff: Filter dataset & variable by a value list of a macro variable

Kurt_Bremser — Thu, 25 Jun 2020 10:15:21 GMT

I will try to lead you to find the answer yourself.

How would you write your data step if you wanted to filter for teams Houston and Cleveland, without any macro reference?

data a2(where=(select=1));
set a1;
select=0;
if mapIDname IN (/* what would you put in here manually? */) then select=1;
run;

Re: Filter dataset & variable by a value list of a macro variable

Kurt_Bremser — Thu, 25 Jun 2020 10:16:14 GMT

BTW Welcome to the communities!

Re: Filter dataset & variable by a value list of a macro variable

coj — Thu, 25 Jun 2020 10:23:44 GMT

Thank you Kurt for the rapid reaction. Your hint suggests to use Tokens within parenthesis. However, the &team macro variable is a variable and contains different numbers of values. This step will be repeated more than 1000 times with different value lists. Thus, i need a solution with macro variable. Any idea Kurt?

Betreff: Filter dataset & variable by a value list of a macro variable

s_lassen — Thu, 25 Jun 2020 11:07:08 GMT

You need to get the values for your IN clause quoted, so that you will have a list like 'team1','team2','team3', not 'team1,team2,team3', and you should use SEPARATED BY to get the list of values:

* Save value list in macro variable ;

proc sql noprint;
select distinct quote(trim(team),"'") into :teams separated by ','
from sashelp.baseball;
quit;

which you can then use in your IN clause:

data a2(where=(select=1)); 
set a1;
select=mapIDname IN (&teams);
run;

But why have the SELECT variable? It is always 1 on the output, anyway. So you should simplify to

data a2;
  set a1;
  where mapIDname in(&teams);
run;

The query will also run faster if you put the WHERE clause on the input table, as shown.

Re: Filter dataset & variable by a value list of a macro variable

PaigeMiller — Thu, 25 Jun 2020 11:07:20 GMT

@coj wrote:
Thank you Kurt for the rapid reaction. Your hint suggests to use Tokens within parenthesis. However, the &team macro variable is a variable and contains different numbers of values. This step will be repeated more than 1000 times with different value lists. Thus, i need a solution with macro variable. Any idea Kurt?

I think Kurt wants you to create a working example with two "tokens" or values, not thousands. This is almost a mandatory first step if you are going to get macros or macro variables to work properly. If you can't get code to work without macros and without macro variables, then you will not be able to get it to work with macros and with macro variables.

So, please provide the answer to a simple question, how would you do this for 2 values, without macros and without macro variables?

if mapIDname IN (/* what would you put in here manually? */) then select=1;

Re: Filter dataset & variable by a value list of a macro variable

Kurt_Bremser — Thu, 25 Jun 2020 11:19:55 GMT

Step 1: start with a working example for one instance, in this case, I take my Houston, Cleveland example.

data a2(where=(select=1));
set a1;
select=0;
if mapIDname IN ('Houston','Cleveland') then select=1;
run;

Since 0 and 1 are the boolean true and false values in SAS, and you only want true, we can simplify this a lot:

data a2;
set a1;
if mapIDname IN ('Houston','Cleveland');
run;

But we see that we can't use

IN ("Houston,Cleveland");

which is what your code would have created if you had used double quotes (macro variables are not resolved when enclosed in single quotes).

So we need to set the quotes when we create the macro variables in the first place:

proc sql noprint;
select distinct quote(trim(team)) into :teams separated by ','
from sashelp.baseball;
quit;

The resulting macro variable can now be used in

data a2;
set a1;
if mapIDname IN (&teams);
run;

But you should also take a step back and look at your process from a certain mental distance. You say you have to repeat that 1000 times; it might be possible (and better overall) to solve that with a single join that creates a grouped output dataset, with each group representing one of your selections.

To see if this can be done, I would like to see examples of your real datasets. Please post i usable fashion (data steps with datalines, or similar, so I can recreate your datasets with copy/paste and submit.

Edit: simplified the SQL code, as done by @s_lassen

Re: Filter dataset & variable by a value list of a macro variable

coj — Thu, 25 Jun 2020 11:50:57 GMT

Thank you Kurt! My missing information was this: "double quotes (macro variables are not resolved when enclosed in single quotes)". Now it is working within my environment due to the [quote(trim(team)) into :teams separated by ','] statement! Thanx a lot! Chris.