Assign as values of a parameter of a macro the values a variable takes

[NewUsrStat]

Hi guys, 

suppose to have the following dataset: 

data DB;
infile datalines;
input ID $ subgrp $ siteid $;
datalines;
001 Y 002
002 N 002
003 N 002
004 Y 002
005 Y 003
006 N 003
;
run;

I would like to give as input values (value_myVar) of my variable (myVar),  the list of sites (siteid). Since they are not unique because there are multiple records per ID, I did the following: 

proc sql noprint;select distinct siteid into :sites separated by ' ' from db;quit;
%put &sites; 

Then I have a macro that looks like this: 

%mymacro(data=db, myVar=siteid, value_myVar=&sites, out=test);

In the macro there is the following condition: 

where &myVar in (&value_myVar);

This was initially intended to select "Y" or "N". In this case the variable takes different values I cannot manually specify because I don't know every time how many and which are. 

So I thought to put all values in &sites to allow the macro to read all available siteids independently from what is my actual knowledge and to run (proc freq) on all of them. 

The following error occurs: 

ERROR: WHERE clause operator requires compatible variables.

I suppose because the &sites content looks like this: 002 003 and where requires: "002", "003" etc. 

Can anyone help me please? 

One note: the output should be the output of proc freq with all sites as rows and relative statistics. 

Finally I cannot remove the "where" condition from the code because in other cases I need to specify "Y" or "N". 

Thank you in advance, 

Best

[Kathryn_SAS · Accepted solution]

Try the following:

994  proc sql noprint;
995  select distinct quote(trim(siteid)) into :sites separated by ' '
996  from db;
997  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


998
999  %put &sites;
"002" "003"

[Tom]

Your SITEID variable is character.

The code you generated was 

where &myVar in (002 003);

Which is trying to look for a character value in a list of numeric values.

To make string literals add quotes.

Best to use single quotes so that macro triggers in the values of SITEID are not acted on by the macro processor.

Note that trailing spaces are not meaningful in comparisons, but would make the text put into the macro variable longer, so remove those.

select distinct quote(trim(SITEID),"'")
  into :SITES separated by ' ' 
  from DB
;

[Kurt_Bremser]

If the WHERE statement is part of a DATA step, it is much better to use a hash object there. A macro variable will reach its maximum size much earlier than a hash object would run out of memory. Note that the hash object can also be used in a DATA step view that serves as input for a procedure.

[Ksharp]

You could change your WHERE statement

where &myVar in (&value_myVar);

into

where  findw("&value_myVar"，"&myVar" ,' ','it');