Multiple characters in a LET statement, then searching

[Seb_A_Sanders]

I have a list of flags that I would like to define in a %LET statement

%LET vcodes = LCI LJL DIB MEH CRB LEB BAS AGE STC DMA;

I then need to filter a couple of tables based on these, I've done this with a string of values, but it doesn't seem to work for characters:

PROC SQL;
   CREATE TABLE codes AS 
   SELECT 
*
      FROM Source_table
      WHERE warning_code IN (&vcodes);
RUN;

I have also tried:

PROC SQL;
   CREATE TABLE codes AS 
   SELECT 
*
      FROM Source_table
      WHERE warning_code IN ("&vcodes");
RUN;

With no luck, is there a trick to this? Why does it work for numeric values, but not character?

Thanks in advance!

[novinosrin]

You are missing the quotes

%LET vcodes = 'LCI' 'LJL' 'DIB';

[Ksharp]

PROC SQL;
   CREATE TABLE codes AS 
   SELECT 
*
      FROM Source_table
      WHERE  findw(symget('vcodes') , strip(warning_code) );
RUN;

[Reeza]

---

@Seb_A_Sanders wrote:

With no luck, is there a trick to this? Why does it work for numeric values, but not character?

Thanks in advance!

---

Your code is not valid SAS syntax.

For character values this resolves to:

where warning_code in (LCI LJL DIB MEH CRB LEB BAS AGE STC DMA);

Which is not valid code. 

You need to add quotes around the values. There are a few ways to fix this, one is to manually add the quotes, another is to change how the macro variable is created so it adds the quotes and another is to modify the macro variable using functions to add the quotes after the fact.

[ballardw]

---

@Seb_A_Sanders wrote:

I have a list of flags that I would like to define in a %LET statement

%LET vcodes = LCI LJL DIB MEH CRB LEB BAS AGE STC DMA;

I then need to filter a couple of tables based on these, I've done this with a string of values, but it doesn't seem to work for characters:

PROC SQL;
   CREATE TABLE codes AS 
   SELECT 
*
      FROM Source_table
      WHERE warning_code IN (&vcodes);
RUN;

I have also tried:

PROC SQL;
   CREATE TABLE codes AS 
   SELECT 
*
      FROM Source_table
      WHERE warning_code IN ("&vcodes");
RUN;

With no luck, is there a trick to this? Why does it work for numeric values, but not character?

Thanks in advance!

---

It does not work for character values because the result is

WHERE warning_code IN ("LCI LJL DIB MEH CRB LEB BAS AGE STC DMA"); 

when what you would need is

WHERE warning_code IN ("LCI" "LJL" "DIB" "MEH" "CRB" "LEB" "BAS" "AGE" "STC" "DMA"); 

Each literal value needs its own set of quotes. Numeric values, not needing quotes don't have this issue with macro substitution.

Here is one of many ways to create a quoted list of space delimited items.

%macro quotevals(vcodes=);
%let temp=;
%do i= 1 %to %sysfunc(countw(&vcodes.));
   %let temp= &temp. %sysfunc(quote(%scan(&vcodes,&i)));
%end;
&temp
%mend;

%let qcodes = %quotevals(vcodes = LCI LJL DIB MEH CRB LEB BAS AGE STC DMA);
%put quoted codes=: &qcodes;