how to pass macro variable to in clause

[current_thing]

I have the following code:

proc sql;
	select distinct "'" || strip(code) || "'" into :process separated by "," from tbl1 where code_type="A";
	create table temp as select * from tbl1 where code in ('&process');
quit;

the above generates a list from the macro variable that matches my expectation. however, temp table generated is blank. what am i missing here? the reason i'm approaching this way is because I need to pass &process to a data step:

data want1 want2;
	set have;
	if code in ("&process") then output want1;
run;

I rather not have to rewrite the existing code, so thought this would be a quick approach

[PaigeMiller]

('&process')

probably will never work. You also need to have double-quotes around macro variables for any of them to be of use.

You need to think about generating working SAS code when the macro variable &process is replaced by its value. So for example, if &process contains the value '2253','2307' as generated from the first line you show of SQL code, then the second line produces this code (assuming you changed the single quotes around &process to double quotes). Show us that re-written code without macro variables that does work and does what you want.

create table temp as select * from tbl1 where code in ("'2253,'2307'");

So now I ask you, if you wrote this line of SAS code yourself, without macros, but exactly as shown, will it do what you want? If not, then re-write this line of SAS code, without macros, to do what you want. (This is a fundamental minimum, you have to be able to write working SAS code without macros; if it doesn't work without macros then it will never work with macros)

[current_thing]

I see what you mean. Well back to drawing board for me. Thanks

[PaigeMiller]

---

@current_thing wrote:

I see what you mean. Well back to drawing board for me. Thanks

---

Maybe you don't see what I mean. Back to the drawing board simply isn't how I would describe it. The change I am hoping you can figure out is a relatively simple change, a syntax change only. And to make things simple, let's suppose there were only two codes that would by used by the IN clause of SQL (your real-world problem may have 100 codes, but suppose there are only two)

You could figure it out by trying to write code without macro variables that works and does what you want.

Suppose you are trying to use PROC SQL to extract data from SASHELP.CARS where the variable MAKE is either 'Nissan' or 'Mazda'. What is that code, without macro variables? I will get you started

create table temp as select * from sashelp.cars where make in ________;

Fill in the blank with working SAS code.

[current_thing]

Sorry, to clarify why I wanted to create a list &process is because I need to pass this macro variable to a data step, like this:

data want1 want2;
	set have;
	if code in ("&process") then output want1;
run;

let me know if that helps

[PaigeMiller]

That does not change a thing. You haven't tried to write code that works without macro variables.

If &process has the value 'ABCD','ABCE' then you get

if code in ("'ABCD','ABCE'") then output want1;

and that will not do what you want. You have a syntax error. Take a minute, and try to figure out what is wrong with that code, and write code that works without macro variables. 

[ballardw]

With macro coding you should show the desired result after the macro variables are resolved. Best, if using data to populated the macro variable, would be to include example data in the form of a working data step.

Showing how to call the macro is not terribly helpful and as @PaigeMiller shows can generate error.

You may want to look up the QUOTE function instead of using that ugly || concatenation code. If you need a single quote character the second parameter to the function will allow you to specify a single quote instead of the default double quote:   quote( strip(code),"'")    (which looks funny but is a single quote inside double quotes)

---

@current_thing wrote:

Sorry, to clarify why I wanted to create a list &process is because I need to pass this macro variable to a data step, like this:

data want1 want2;
	set have;
	if code in ("&process") then output want1;
run;

let me know if that helps

---

[Quentin]

@current_thing  your code is very close.  Looks like @PaigeMiller is happy to help you worth through this via a Socratic method, which is very helpful for many macro programming questions.

[Kurt_Bremser]

Your macro variable already contains the necessary quotes. Why do you use additional quotes around it?

[Tom]

Let's list the problems with your approach.

The main issue is that you put a string like

'A','B','C'

into the macro variable and then used it to generate code like:

if code in ("'A','B','C'") then ....

So it is looking for 'A','B','C' all in one value.  Which probably is NOT what you want.  So remove those outer quotes.  

The next problem is the use of the STRIP() function.  That will remove the any leading spaces from the values.  So there is no way you could match a value that has leading spaces, like '   A', since you would generate the macro variable without the leading spaces.  Just use the TRIM() function instead.

The logic of your data step is strange also.  You are writing two datasets, but the second never gets any observations written to it.  Perhaps you just missed the ELSE clause?

Use the QUOTE() function to add the quotes.  That way if the values already have quotes in them you get a valid string constant generated instead of unbalanced quotes.  Use the optional second argument to QUOTE() so that the result is quoted with single quotes.  That way any % or & characters in the values don't accidently get seen by the macro processor.

You probably do not want to write all of the values to the OUTPUT window. So include the NOPRINT option on the PROC SQL statement.

Also you can simplify by using space instead of comma between the items in the macro variable.  The IN operator in SAS is just as happy with spaces.  And it is much easier to work with macro variables that have spaces than those with commas in them.  You can pass them in as values of parameters in macro calls without having to use macro quoting.

proc sql noprint;
  select distinct quote(trim(code),"'") 
    into :process separated by ' ' 
    from tbl1 
    where code_type="A"
  ;
  create table temp as 
    select *
    from tbl1 
    where code in (&process)
  ;
quit;

data want1 want2;
  set have;
  if code in (&process) then output want1;
  else output want2 ;
run;