Data types error when using "select into" for a character variable

[linlin87]

Hi SAS Community,

I am using the following code to pull make a list and lookup another dataset with this list:

proc sql;
select distinct charvar1 into :cvv1 separated by "," from dataset1;
quit;

proc sql; 
create table output1 as 
select *
from dataset2
where charvar1 in (&cv1);
quit;

but I get the following error:

ERROR: Expression using IN has components that are of different
       data types.
NOTE: The IN referred to may have been transformed from an OR to
      an IN at some point during PROC SQL WHERE clause
      optimization.

I have checked the charvar1 and in both dataset1 and dataset2 it has the format $16. so I don't understand why it is saying that they are different types. Any help would be really great!

[PeterClemmensen · Accepted solution]

Try this

proc sql;
   select distinct quote(strip(charvar1)) 
   into :cvv1 separated by "," 
   from dataset1;
quit;

[linlin87]

Thank you Peter. Why did the original not work and why does you fix work?

[PeterClemmensen]

Anytime. 

Suppose charvar1 contains the values 'a', 'b' and 'c. Your code creates the macro variable a,b,c. However, SAS interprets a as a variable name and 'a' as a character value. You want SAS to interpret as character values.

[ballardw]

Your error is one of the reasons you should copy the entire code that generates the error from the LOG along with the error messages. The "code" that you show cannot create that error unless there is much more going on because the code you posted doesn't use the macro variable created in the first step. The code shown would create a variable named CVV1 but you use CV1 in the following code.

proc sql;
select distinct charvar1 into :cvv1 separated by "," from dataset1;
quit;

proc sql; 
create table output1 as 
select *
from dataset2
where charvar1 in (&cv1);
quit;

---

@linlin87 wrote:

Hi SAS Community,

I am using the following code to pull make a list and lookup another dataset with this list:

proc sql;
select distinct charvar1 into :cvv1 separated by "," from dataset1;
quit;

proc sql; 
create table output1 as 
select *
from dataset2
where charvar1 in (&cv1);
quit;

but I get the following error:

ERROR: Expression using IN has components that are of different
       data types.
NOTE: The IN referred to may have been transformed from an OR to
      an IN at some point during PROC SQL WHERE clause
      optimization.

I have checked the charvar1 and in both dataset1 and dataset2 it has the format $16. so I don't understand why it is saying that they are different types. Any help would be really great!

---

[linlin87]

Yes